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
