March 20, 2013 at 3:31 am
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>
March 20, 2013 at 12:48 pm
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
March 20, 2013 at 3:40 pm
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
March 20, 2013 at 10:53 pm
But how can I pick the fields in this way ?
March 21, 2013 at 2:02 am
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
March 21, 2013 at 2:16 am
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:
You'll need to define an XML collection. CREATE XML COLLECTION
March 22, 2013 at 3:20 am
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]
March 22, 2013 at 8:06 am
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/61537Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy