January 23, 2013 at 1:39 am
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.
January 23, 2013 at 2:09 am
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
January 23, 2013 at 2:24 am
Thank you arthurolcot very very much! That did exactly what I was looking for.
Many thanks
David
January 23, 2013 at 2:41 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply