Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to read XML file with multiple children and load it in SQL Server Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 3:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 3:48 AM
Points: 49, Visits: 237
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
Posted Wednesday, March 20, 2013 12:48 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:55 AM
Points: 1,288, Visits: 1,863
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
Posted Wednesday, March 20, 2013 3:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 13,251, Visits: 11,030
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
Posted Wednesday, March 20, 2013 10:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 3:48 AM
Points: 49, Visits: 237
But how can I pick the fields in this way ?
Post #1433585
Posted Thursday, March 21, 2013 2:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 13,251, Visits: 11,030
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
Posted Thursday, March 21, 2013 2:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:55 AM
Points: 1,288, Visits: 1,863
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
Posted Friday, March 22, 2013 3:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 3:48 AM
Points: 49, Visits: 237
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
Posted Friday, March 22, 2013 8:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:38 AM
Points: 1,678, Visits: 19,553
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 »

Add to briefcase

Permissions Expand / Collapse