Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Another XML to tabular question


Another XML to tabular question

Author
Message
hb21l6
hb21l6
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 294
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.
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 1777
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
hb21l6
hb21l6
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 294
Thank you arthurolcot very very much! That did exactly what I was looking for.


Many thanks
David
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 1777
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search