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

Another XML to tabular question Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 1:39 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 10:47 AM
Points: 81, Visits: 280
Hello 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:
<?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>

I can load the XML file into a #temp table


CREATE TABLE XmlImportTest
(
xmlFileName VARCHAR(300),
xml_data xml
)
GO

DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'E:\Orders.xml'
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData
FROM
(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO



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.


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
from
xmlImportTest
cross apply
xml_data.nodes('/DsOrders/Cust') as Cust(C)



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!


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
from
xmlImportTest
cross apply
xml_data.nodes('/DsOrders/Cust') as Cust(C)
cross apply
xml_data.nodes('/DsOrders/Cust/OrderItems') as Items(D)



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.






Post #1410398
Posted Wednesday, January 23, 2013 2:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:36 AM
Points: 2,419, Visits: 1,565
Hi,
I think you are very close, does this updated final query do what you need it to do or get you closer?

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
from
xmlImportTest
cross apply xml_data.nodes('/AsOrders/Cust') as Cust(C)
CROSS APPLY C.nodes('OrderItems') AS Items(I)


This returns three records, two for order 165237 and one for order 165236
Post #1410413
Posted Wednesday, January 23, 2013 2:24 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 10:47 AM
Points: 81, Visits: 280
Thank you arthurolcot very very much! That did exactly what I was looking for.


Many thanks
David
Post #1410418
Posted Wednesday, January 23, 2013 2:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:36 AM
Points: 2,419, Visits: 1,565
hb21l6 (1/23/2013)
Thank you arthurolcot very very much! That did exactly what I was looking for.


Many thanks
David


No problem.. Thanks for the feedback.
Post #1410424
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse