October 30, 2009 at 9:08 am
Hi,
I have a xml whose structure is as below:
<ns:abcd-XMLInterchange xmlns:dc="http://axc.net/dc/elements/1.1/" xmlns:dcq="http://axc.net/dc/terms/" xmlns:gms="http://www.abcded.gov/CM/gms" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.abcd.us/DataStandards/XMLschema/acd/ns https://edt-schema.uk/schema/acd/6.0/XML_Message_Root-V6-0-2008-01-14.xsd" SchemaVersion="6-0" SchemaDate="2008-01-14" xmlns:ns="http://www.test.us/DataStandards/XMLschema/ns">
<ns:Header>
<ns:PersonGeneralinfo>
<ns:Name>abcded</ns:Name>
<ns:Country>abcded</ns:Country>
<ns:OrderInformation>
<ns:OrderID>123</ns:OrderID>
<ns:OrderID>4564</ns:OrderID>
</ns:OrderInformation>
</ns:PersonGeneralinfo>
<ns:PersonOtherInfo>
<ns:Info1>abcded</ns:Info1>
</ns:PersonOtherInfo>
</ns:Header>
</ns:abcd-XMLInterchange>
I want to load the above xml into the SQL tables - Table Person with fields Name,Country & Info1. Table Orders with fields OrderID (multiple rows)
How do I do this above in ssis? If it is a simple xml then xmlsource works fine.
Any technical suggestion is appreciated.
Thanks
Suga
October 30, 2009 at 11:41 am
Since you have a typed xml file, you need to use the NAMESPACE declaration.
I would use T-SQL rather than SSIS though. Therefore I'll show you an T-SQL example:
;WITH XMLNAMESPACES (
'http://www.test.us/DataStandards/XMLschema/ns' AS ns
)
SELECT tab.col.value('ns:Name[1]','nvarchar(50)') AS Name
INTO Person
FROM @xml.nodes('ns:abcd-XMLInterchange/ns:Header/ns:PersonGeneralinfo')AS tab(col)
SELECT * FROM Person
/* result set:
Name
abcded
*/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply