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

Question

Post reply

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

SQL Kidu

Ten Centuries

Points: 1296
More actions
March 20, 2013 at 3:31 am

#271801

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>

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AlexSQLForums SSChampion Points: 14277 More actions · Answer 1

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 SSC Guru Points: 259085 More actions · Answer 2

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[/url]

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SQL Kidu Ten Centuries Points: 1296 More actions · Answer 3

SQL Kidu

Ten Centuries

Points: 1296

March 20, 2013 at 10:53 pm

#1599222

But how can I pick the fields in this way ?

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 4

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

AlexSQLForums SSChampion Points: 14277 More actions · Answer 5

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[/url]

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

Alex S

SQL Kidu Ten Centuries Points: 1296 More actions · Answer 6

Roughly like ;

[font="Courier New"]stringstring2string3string4string5intint6anyType

Orders.xml_KA-ORDRS-21-Jan-2013

OrderDetails.bin_KJ-18-Mar-2013-WEGradedWarehouseEntryTSWearhouseEntryTS25-Jul-12 8:24:35 PM2

OrderDetails.bin_KJ-18-Mar-2013-WEGradedWarehouse TypeClosed and RoofedType A143

OrderDetails.bin_KJ-18-Mar-2013-WEGradedDLCRDLCR000085401

OrderDetails.bin_KJ-18-Mar-2013-WEGradedCandF_SectionForwarding SectionsPEandPaperCorrugated10

OrderDetails.bin_KJ-18-Mar-2013-WEGradedPackingAndMovementPackingAndMovement5 X 6 Feet , With godowns10

PendingOrders.bin_KA-UE-04-Feb-2013WearhouseEntryTSWearhouseEntryTS25-Jan-13 8:10:15 PM20

PendingOrders.bin_KA-UE-04-Feb-2013Warehouse TypeWarehouse Typev8.010

PendingOrders.bin_KA-UE-04-Feb-2013DLCRDLCR0000854010

PendingOrders.bin_KA-UE-04-Feb-2013Forwarding SectionsForwarding SectionsElectricalCarriage10

PendingOrders.bin_KA-UE-04-Feb-2013PackingAndMovementPackingAndMovement5 X 6 Feet , Without godowns10

PendingOrders.bin_KA-UE-04-Feb-2013QuantityTransferQuantityTransferyes10

PendingOrders.bin_KA-UE-04-Feb-2013StoreIDStoreIDFootware10

PendingOrders.bin_KA-UE-04-Feb-2013SodexoSodexoSodexo10

PendingOrders.bin_KA-UE-04-Feb-2013AC HeadAC Head110

[/font]

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 7

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