Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to read XML file with multiple children and load it in SQL Server


How to read XML file with multiple children and load it in SQL Server

Author
Message
SQL Kidu
SQL Kidu
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 250
I have an XML file in the below given format. How can I import it into SQL Server using OPENXML or some other method ?
----------------------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="utf-8"?>
<SRCL>
<list>
<AOSRC xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SRC>
<SRCNm>
<string>Orders.xml_</string>
</SRCNm>
<SRCHsh>
<string>KA-ORDRS-21-Jan-2013</string>
</SRCHsh>
<SRCRslts>
<XSDOS />
</SRCRslts>
</SRC>
<SRC>
<SRCNm>
<string>OrderDetails.bin_</string>
</SRCNm>
<SRCHsh>
<string>KJ-18-Mar-2013-WEGraded</string>
</SRCHsh>
<SRCRslts>
<XSDOS>
<item>
<FieldNm>
<string>WarehouseEntryTS</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>WearhouseEntryTS</string>
</name>
<value>
<string>25-Jul-12 8:24:35 PM</string>
</value>
<weight>
<int>2</int>
</weight>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>Warehouse Type</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>Closed and Roofed</string>
</name>
<value>
<string>Type A</string>
</value>
<weight>
<int>143</int>
</weight>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>DLCR</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>DLCR</string>
</name>
<value>
<string>00008540</string>
</value>
<weight>
<int>1</int>
</weight>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>CandF_Section</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>Forwarding Sections</string>
</name>
<value>
<string>PEandPaperCorrugated</string>
</value>
<weight>
<int>1</int>
</weight>
<ConvertibleYN>
<int>0</int>
</ConvertibleYN>
<tag>
<anyType xsi:nil="true" />
</tag>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>PackingAndMovement</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>PackingAndMovement</string>
</name>
<value>
<string>5 X 6 Feet , With godowns</string>
</value>
<weight>
<int>1</int>
</weight>
<ConvertibleYN>
<int>0</int>
</ConvertibleYN>
<tag>
<anyType xsi:nil="true" />
</tag>
</SRlt>
</value>
</item>
</XSDOS>
</SRCRslts>
</SRC>
<SRC>
<SRCNm>
<string>PendingOrders.bin_</string>
</SRCNm>
<SRCHsh>
<string>KA-UE-04-Feb-2013</string>
</SRCHsh>
<SRCRslts>
<XSDOS>
<item>
<FieldNm>
<string>WearhouseEntryTS</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>WearhouseEntryTS</string>
</name>
<value>
<string>25-Jan-13 8:10:15 PM</string>
</value>
<weight>
<int>2</int>
</weight>
<ConvertibleYN>
<int>0</int>
</ConvertibleYN>
<tag>
<anyType xsi:nil="true" />
</tag>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>Warehouse Type</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>Warehouse Type</string>
</name>
<value>
<string>v8.0</string>
</value>
<weight>
<int>1</int>
</weight>
<ConvertibleYN>
<int>0</int>
</ConvertibleYN>
<tag>
<anyType xsi:nil="true" />
</tag>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>DLCR</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>DLCR</string>
</name>
<value>
<string>00008540</string>
</value>
<weight>
<int>1</int>
</weight>
<ConvertibleYN>
<int>0</int>
</ConvertibleYN>
<tag>
<anyType xsi:nil="true" />
</tag>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>Forwarding Sections</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>Forwarding Sections</string>
</name>
<value>
<string>ElectricalCarriage</string>
</value>
<weight>
<int>1</int>
</weight>
<ConvertibleYN>
<int>0</int>
</ConvertibleYN>
<tag>
<anyType xsi:nil="true" />
</tag>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>PackingAndMovement</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>PackingAndMovement</string>
</name>
<value>
<string>5 X 6 Feet , Without godowns</string>
</value>
<weight>
<int>1</int>
</weight>
<ConvertibleYN>
<int>0</int>
</ConvertibleYN>
<tag>
<anyType xsi:nil="true" />
</tag>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>QuantityTransfer</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>QuantityTransfer</string>
</name>
<value>
<string>yes</string>
</value>
<weight>
<int>1</int>
</weight>
<ConvertibleYN>
<int>0</int>
</ConvertibleYN>
<tag>
<anyType xsi:nil="true" />
</tag>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>StoreID</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>StoreID</string>
</name>
<value>
<string>Footware</string>
</value>
<weight>
<int>1</int>
</weight>
<ConvertibleYN>
<int>0</int>
</ConvertibleYN>
<tag>
<anyType xsi:nil="true" />
</tag>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>Sodexo</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>Sodexo</string>
</name>
<value>
<string>Sodexo</string>
</value>
<weight>
<int>1</int>
</weight>
<ConvertibleYN>
<int>0</int>
</ConvertibleYN>
<tag>
<anyType xsi:nil="true" />
</tag>
</SRlt>
</value>
</item>
<item>
<FieldNm>
<string>AC Head</string>
</FieldNm>
<value>
<SRlt>
<name>
<string>AC Head</string>
</name>
<value>
<string>1</string>
</value>
<weight>
<int>1</int>
</weight>
<ConvertibleYN>
<int>0</int>
</ConvertibleYN>
<tag>
<anyType xsi:nil="true" />
</tag>
</SRlt>
</value>
</item>
</XSDOS>
</SRCRslts>
</SRC>
</AOSRC>
</list>
</SRCL>
AlexSQLForums
AlexSQLForums
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1361 Visits: 2249
You can dump it into 1 row as a single blob and then use select for xml to manipulate it.

create table docs (pk int primary key, xcol xml not null)
insert into docs
select 10, xCol from (select * from openrowset(bulk 'D:\junk\xml.xml', single_blob) as xcol) as r(xcol)

declare @xvar xml
set @xvar = (select * from docs for xml auto, type)
select @xvar

Alex S
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16538 Visits: 13210
Another option is to use XSLT to transform the XML file to an easier format, such as .csv.
An example:
Loading Complex XML Using SSIS



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
SQL Kidu
SQL Kidu
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 250
But how can I pick the fields in this way ?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16538 Visits: 13210
SQL Kidu (3/20/2013)
But how can I pick the fields in this way ?


Can you be a bit more specific? Desired output would be welcome.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
AlexSQLForums
AlexSQLForums
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1361 Visits: 2249
Koen Verbeeck (3/20/2013)
Another option is to use XSLT to transform the XML file to an easier format, such as .csv.
An example:
Loading Complex XML Using SSIS

You'll need to define an XML collection. CREATE XML COLLECTION

Alex S
SQL Kidu
SQL Kidu
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 250
Roughly like ;

string string2 string3 string4 string5 int int6 anyType
Orders.xml_ KA-ORDRS-21-Jan-2013
OrderDetails.bin_ KJ-18-Mar-2013-WEGraded WarehouseEntryTS WearhouseEntryTS 25-Jul-12 8:24:35 PM 2
OrderDetails.bin_ KJ-18-Mar-2013-WEGraded Warehouse Type Closed and Roofed Type A 143
OrderDetails.bin_ KJ-18-Mar-2013-WEGraded DLCR DLCR 00008540 1
OrderDetails.bin_ KJ-18-Mar-2013-WEGraded CandF_Section Forwarding Sections PEandPaperCorrugated 1 0
OrderDetails.bin_ KJ-18-Mar-2013-WEGraded PackingAndMovement PackingAndMovement 5 X 6 Feet , With godowns 1 0
PendingOrders.bin_ KA-UE-04-Feb-2013 WearhouseEntryTS WearhouseEntryTS 25-Jan-13 8:10:15 PM 2 0
PendingOrders.bin_ KA-UE-04-Feb-2013 Warehouse Type Warehouse Type v8.0 1 0
PendingOrders.bin_ KA-UE-04-Feb-2013 DLCR DLCR 00008540 1 0
PendingOrders.bin_ KA-UE-04-Feb-2013 Forwarding Sections Forwarding Sections ElectricalCarriage 1 0
PendingOrders.bin_ KA-UE-04-Feb-2013 PackingAndMovement PackingAndMovement 5 X 6 Feet , Without godowns 1 0
PendingOrders.bin_ KA-UE-04-Feb-2013 QuantityTransfer QuantityTransfer yes 1 0
PendingOrders.bin_ KA-UE-04-Feb-2013 StoreID StoreID Footware 1 0
PendingOrders.bin_ KA-UE-04-Feb-2013 Sodexo Sodexo Sodexo 1 0
PendingOrders.bin_ KA-UE-04-Feb-2013 AC Head AC Head 1 1 0
[size="2"][/size]

Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 22812
You can shred your XML using something like this


declare @xvar xml=
'<SRCL>
<list>
.
.
</list>
</SRCL> ';



SELECT l1.v1.value('(SRCNm/string/text())[1]','VARCHAR(100)') as string1,
l1.v1.value('(SRCHsh/string/text())[1]','VARCHAR(100)') as string2,
l2.v2.value('(FieldNm/string/text())[1]','VARCHAR(100)') as string3,
l2.v2.value('(value/SRlt/name/string/text())[1]','VARCHAR(100)') as string4,
l2.v2.value('(value/SRlt/value/string/text())[1]','VARCHAR(100)') as string5,
l2.v2.value('(value/SRlt/weight/int/text())[1]','INT') as [int],
l2.v2.value('(value/SRlt/ConvertibleYN/int/text())[1]','INT') as [int6],
l2.v2.value('(value/SRlt/tag/anyType/text())[1]','VARCHAR(100)') as anyType
FROM @xvar .nodes('/SRCL/list/AOSRC/SRC') AS l1(v1)
OUTER APPLY l1.v1.nodes('SRCRslts/XSDOS/item') AS l2(v2);



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search