InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 23:21:13 GMT20http://www.sqlservercentral.com/Forums/Topic1433083-392-1.aspxYou can shred your XML using something like this[code="sql"]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 anyTypeFROM @xvar .nodes('/SRCL/list/AOSRC/SRC') AS l1(v1)OUTER APPLY l1.v1.nodes('SRCRslts/XSDOS/item') AS l2(v2);[/code]Fri, 22 Mar 2013 08:06:19 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1433083-392-1.aspxRoughly like ;[font="Courier New"]string string2 string3 string4 string5 int int6 anyTypeOrders.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][/font]Fri, 22 Mar 2013 03:20:10 GMTSQL Kiduhttp://www.sqlservercentral.com/Forums/Topic1433083-392-1.aspx[quote][b]Koen Verbeeck (3/20/2013)[/b][hr]Another option is to use XSLT to transform the XML file to an easier format, such as .csv.An example:[url=http://blog.hoegaerden.be/2011/04/20/loading-complex-xml-using-ssis/]Loading Complex XML Using SSIS[/url][/quote]You'll need to define an XML collection. [url=http://msdn.microsoft.com/en-us/library/ms176009.aspx]CREATE XML COLLECTION[/url]Thu, 21 Mar 2013 02:16:24 GMTAlexSQLForumshttp://www.sqlservercentral.com/Forums/Topic1433083-392-1.aspx[quote][b]SQL Kidu (3/20/2013)[/b][hr]But how can I pick the fields in this way ?[/quote]Can you be a bit more specific? Desired output would be welcome.Thu, 21 Mar 2013 02:02:11 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1433083-392-1.aspxBut how can I pick the fields in this way ?Wed, 20 Mar 2013 22:53:04 GMTSQL Kiduhttp://www.sqlservercentral.com/Forums/Topic1433083-392-1.aspxAnother option is to use XSLT to transform the XML file to an easier format, such as .csv.An example:[url=http://blog.hoegaerden.be/2011/04/20/loading-complex-xml-using-ssis/]Loading Complex XML Using SSIS[/url]Wed, 20 Mar 2013 15:40:10 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1433083-392-1.aspxYou 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 xmlset @xvar = (select * from docs for xml auto, type)select @xvarWed, 20 Mar 2013 12:48:52 GMTAlexSQLForumshttp://www.sqlservercentral.com/Forums/Topic1433083-392-1.aspxI 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>Wed, 20 Mar 2013 03:31:51 GMTSQL Kidu