InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 13:06:30 GMT20http://www.sqlservercentral.com/Forums/Topic1410398-21-1.aspx[quote][b]hb21l6 (1/23/2013)[/b][hr]Thank you arthurolcot very very much! That did exactly what I was looking for.Many thanksDavid[/quote]No problem.. Thanks for the feedback.Wed, 23 Jan 2013 02:41:19 GMTarthurolcothttp://www.sqlservercentral.com/Forums/Topic1410398-21-1.aspxThank you arthurolcot very very much! That did exactly what I was looking for.Many thanksDavidWed, 23 Jan 2013 02:24:19 GMThb21l6http://www.sqlservercentral.com/Forums/Topic1410398-21-1.aspxHi,I think you are very close, does this updated final query do what you need it to do or get you closer?[code="sql"]select C.value('(Customers/CustomerID)[1]','INT') as CusID, C.value('(Customers/ContactName)[1]','varchar(50)') as ContactName, C.value('(Customers/Email)[1]','varchar(150)') as eml, C.value('(Order/OrderNumber)[1]','INT') as OrderNo, C.value('(Order/OrderDate)[1]','varchar(20)') as OrderDate, i.value('(Description)[1]','varchar(120)') as ItemDesc, i.value('(Price)[1]','money') as ItemPrice, i.value('(ProductCode)[1]','INT') as ProdCode, i.value('(ColourCodeId)[1]','varchar(120)') as ColCode, i.value('(ColourCodeTitle)[1]','varchar(120)') as ColCodeTitle, i.value('(Quantity)[1]','float(20)') as ItemQty Into Tbl_NewTableName fromxmlImportTestcross apply xml_data.nodes('/AsOrders/Cust') as Cust(C)CROSS APPLY C.nodes('OrderItems') AS Items(I)[/code]This returns three records, two for order 165237 and one for order 165236Wed, 23 Jan 2013 02:09:52 GMTarthurolcothttp://www.sqlservercentral.com/Forums/Topic1410398-21-1.aspxHello All, I'm having a bit of a problem getting my XML into a correct tabular format.Below, I have a Customer, order, orderItems XML file.When I write the results out in tabular format, its only taking one Order Item, and not all of them.Heres the data sample:[code="plain"]<?xml version="1.0" standalone="yes"?><AsOrders> <Cust Name='165237'> <Customers> <CustomerID>17335</CustomerID> <ContactName>Amy surname</ContactName> <Email>asurname@Email.com</Email> </Customers> <Order> <OrderNumber>165237</OrderNumber> <OrderDate>14/01/2013 13:36:33</OrderDate> </Order> <OrderItems> <Description>SALE - RED </Description> <Price>6.50</Price> <ProductCode>343</ProductCode> <ColourCodeId>N/A</ColourCodeId> <ColourCodeTitle>N/A</ColourCodeTitle> <Quantity>1</Quantity> </OrderItems> <OrderItems> <Description>SALE - BLUE </Description> <Price>6.50</Price> <ProductCode>344</ProductCode> <ColourCodeId>N/A</ColourCodeId> <ColourCodeTitle>N/A</ColourCodeTitle> <Quantity>1</Quantity> </OrderItems> </Cust> <Cust Name='165236'> <Customers> <CustomerID>16154</CustomerID> <ContactName>Loretta surname</ContactName> <Email>Lorettasurname@email.com</Email> </Customers> <Order> <OrderNumber>165236</OrderNumber> <OrderDate>14/01/2013 13:23:57</OrderDate> </Order> <OrderItems> <Description>SAL GREEN</Description> <Price>72.00</Price> <ProductCode>274</ProductCode> <ColourCodeId>N/A</ColourCodeId> <ColourCodeTitle>N/A</ColourCodeTitle> <Quantity>2</Quantity> </OrderItems> </Cust></AsOrders>[/code]I can load the XML file into a #temp table [code="plain"]CREATE TABLE XmlImportTest( xmlFileName VARCHAR(300), xml_data xml)GODECLARE @xmlFileName VARCHAR(300)SELECT @xmlFileName = 'E:\Orders.xml'-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSETEXEC('INSERT INTO XmlImportTest(xmlFileName, xml_data)SELECT ''' + @xmlFileName + ''', xmlData FROM( SELECT * FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)')GO[/code]Now read the data out of the XML field into a tabular format, and into a new table.( this is where the fault is, its only returning a sinlge order line, with a single item.[code="plain"]select C.value('(Customers/CustomerID)[1]','INT') as CusID, C.value('(Customers/ContactName)[1]','varchar(50)') as ContactName, C.value('(Customers/Email)[1]','varchar(150)') as eml, C.value('(Order/OrderNumber)[1]','INT') as OrderNo, C.value('(Order/OrderDate)[1]','varchar(20)') as OrderDate, C.value('(OrderItems/Description)[1]','varchar(120)') as ItemDesc, C.value('(OrderItems/Price)[1]','money') as ItemPrice, C.value('(OrderItems/ProductCode)[1]','INT') as ProdCode, C.value('(OrderItems/ColourCodeId)[1]','varchar(120)') as ColCode, C.value('(OrderItems/ColourCodeTitle)[1]','varchar(120)') as ColCodeTitle, C.value('(OrderItems/Quantity)[1]','float(20)') as ItemQty Into Tbl_NewTableName fromxmlImportTestcross apply xml_data.nodes('/DsOrders/Cust') as Cust(C)[/code]Now, I'm sure its to do with the cross apply, so i tried the following, but this lists a single customer / order and every order item in the file![code="plain"]select C.value('(Customers/CustomerID)[1]','INT') as CusID, C.value('(Customers/ContactName)[1]','varchar(50)') as ContactName, C.value('(Customers/Email)[1]','varchar(150)') as eml, C.value('(Order/OrderNumber)[1]','INT') as OrderNo, C.value('(Order/OrderDate)[1]','varchar(20)') as OrderDate, D.value('(Description)[1]','varchar(120)') as ItemDesc, D.value('(Price)[1]','money') as ItemPrice, D.value('(ProductCode)[1]','INT') as ProdCode, D.value('(ColourCodeId)[1]','varchar(120)') as ColCode, D.value('(ColourCodeTitle)[1]','varchar(120)') as ColCodeTitle, D.value('(Quantity)[1]','float(20)') as ItemQty Into Tbl_NewTableName fromxmlImportTestcross apply xml_data.nodes('/DsOrders/Cust') as Cust(C)cross apply xml_data.nodes('/DsOrders/Cust/OrderItems') as Items(D)[/code]I've been looking at this one, on and off for the last week and really need some guidance..As you can see, XML isn't my strongest point.Thanks in advance!David.Wed, 23 Jan 2013 01:39:55 GMThb21l6