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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » How to read XML file with multiple children...

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

Rate Topic

Display Mode

Topic Options

Author

Message

SQL Kidu

Posted Wednesday, March 20, 2013 3:31 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 22, 2013 6:04 AM
Points: 46, Visits: 205

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>

Post #1433083

AlexSQLForums

Posted Wednesday, March 20, 2013 12:48 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 1,227, Visits: 1,712

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

Post #1433422

Koen Verbeeck

Posted Wednesday, March 20, 2013 3:40 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:51 AM
Points: 9,373, Visits: 6,470

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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1433513

SQL Kidu

Posted Wednesday, March 20, 2013 10:53 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 22, 2013 6:04 AM
Points: 46, Visits: 205

But how can I pick the fields in this way ?

Post #1433585

Koen Verbeeck

Posted Thursday, March 21, 2013 2:02 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:51 AM
Points: 9,373, Visits: 6,470

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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1433620

AlexSQLForums

Posted Thursday, March 21, 2013 2:16 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 1,227, Visits: 1,712

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

Post #1433625

SQL Kidu

Posted Friday, March 22, 2013 3:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 22, 2013 6:04 AM
Points: 46, Visits: 205

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]

Post #1434161

Mark-101232

Posted Friday, March 22, 2013 8:06 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 1,500, Visits: 18,187

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);

____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #1434287

« Prev Topic | Next Topic »

Permissions