Importing Online XML file into SQL Server 2005 tables – no data transferred

Does anyone have any great suggestions on how I can import an online XML file into an SQL 2005 table?

So far I tried to accomplish it with SSIS Data Flow Task where my source is XML Source (Data access mode: XML file location; XML location: URL, Use inline schema = True). This set up properly identified the columns to be imported.

I used Copy Column data flow transformation task to load data to OLE DB destination table that has same structure.

When I run the task it does execute with no errors, however the table remains empty.It looks like I am failing to read the actual data.

Do you have any suggestions?I am willing to go around this approach with stored procs/com/you name it – just make it work!

Thanks a lot for your help!

LoveDanger

First of all, does any data enter the pipeline from the XM source? Use a data-flow viewer to find out.

-Jamie

|||Nope. No rows affected during the execution.. When I work with the data flow viewer, and point to the URL in the XML Source, it does read the columns of the XML output. Doesn't read the data though..|||

Jamie,

Thanks for the reply. Here is more: I copied xml and tried to import it into my table using sp_xml_prepareDocument ...OPENXML.

I still got the same result: 0 rows affected. Any suggestions? The table structure is identical to the XML output. The OpenXML qry uses the following syntax:

FROM OPENXML(@.xmlHndAdd, '/NewDataSet/Table1', 1)
WITH MyTbl

The XML file format is:

<NewDataSet xmlns="">
<Table1 diffgr:id="Table11" msdata:rowOrder="0">
<program_id>1-2-3-4-5</program_id>
<object_name />
</Table1>
<Table1 diffgr:id="Table12" msdata:rowOrder="1">
<object_id>6-7-8-9-0</object_id>
<object_name>ABC</object_name>
<objectproperty_id>1-3-5-7-9</objectproperty_id>
</Table1>

Thank you!

|||I have the same setup that works without "diffgr:id="TableN" msdata:rowOrder..." if I simply set <table1> - the rows can be inserted just fine... Any more suggestions?|||

Oh.. it was a matter of syntax. Here is the answer:

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE MyTbl

INSERT MyTbl SELECT * FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)

WITH MyTbl