Shredding xml ( parent child relation)

Question

Post reply

Shredding xml ( parent child relation)

Coriolan

SSCommitted

Points: 1931
More actions
June 28, 2013 at 8:35 pm

#295751

I have the following xml.
DECLARE @string VARCHAR(max);
DECLARE @xml XML;
SET @string ='<ShipmentConfirmationMessage>
<ExternalCorrelationId>249801</ExternalCorrelationId>
<ShippedItems>
<ShipmentConfirmationLine>
<SalesOrderNumber>SalesOrder1</SalesOrderNumber>
<ItemId>10982</ItemId>
<IsBackOrdered>false</IsBackOrdered>
<TrackingNumber>Track1234</TrackingNumber>
<Lots>
<Lot>
<LotNumber>789</LotNumber>
<ExpiryDate>2013-12-20T00:00:00</ExpiryDate>
<Quantity>1.55</Quantity>
</Lot>
<Lot>
<LotNumber>456</LotNumber>
<ExpiryDate>2014-10-15T00:00:00</ExpiryDate>
<Quantity>2.45</Quantity>
</Lot>
<Lot>
<LotNumber>999</LotNumber>
<ExpiryDate>2014-12-15T00:00:00</ExpiryDate>
<Quantity>9.99</Quantity>
</Lot>
</Lots>
</ShipmentConfirmationLine>
<ShipmentConfirmationLine>
<SalesOrderNumber>SalesOrder2</SalesOrderNumber>
<ItemId>10983</ItemId>
<IsBackOrdered>true</IsBackOrdered>
<TrackingNumber>Track123456789</TrackingNumber>
<Lots>
<Lot>
<LotNumber>1</LotNumber>
<ExpiryDate>2013-12-20T00:00:00</ExpiryDate>
<Quantity>3.30</Quantity>
</Lot>
<Lot>
<LotNumber>4</LotNumber>
<ExpiryDate>2016-10-15T00:00:00</ExpiryDate>
<Quantity>4.40</Quantity>
</Lot>
</Lots>
</ShipmentConfirmationLine>
</ShippedItems>
</ShipmentConfirmationMessage>
';
SET @xml = CONVERT(XML, @string, 1);
-- header
SELECT
@xml.value('/ShipmentConfirmationMessage[1]/ExternalCorrelationId[1]', 'varchar(50)') PurchaseOrderID
-- detail lines
SELECT IDENTITY(int, 1,1) AS RowID
,doc.col.value('SalesOrderNumber[1]', 'varchar(50)') SalesOrderNumber
,doc.col.value('ItemId[1]', 'varchar(50)') ItemId
,doc.col.value('IsBackOrdered[1]', 'varchar(50)') IsBackOrdered
,doc.col.value('TrackingNumber[1]', 'varchar(50)') TrackingNumber
INTO #tmpDetails
FROM @xml.nodes('/ShipmentConfirmationMessage/ShippedItems/ShipmentConfirmationLine') doc(col)
select * from #tmpDetails
-- lots associated with detail lines
SELECT
doc.col.value('LotNumber[1]', 'varchar(50)') LotNumber
,doc.col.value('ExpiryDate[1]', 'datetime') ExpiryDate
,doc.col.value('Quantity[1]', 'decimal(13,5)') Quantity
FROM @xml.nodes('/ShipmentConfirmationMessage/ShippedItems/ShipmentConfirmationLine/Lots/Lot') doc(col)
drop table #tmpDetails
If you run the SQL query results will look as below:
Grid results A
RowIDSalesOrderNumberItemIdIsBackOrderedTrackingNumber
1SalesOrder110982false Track1234
2SalesOrder210983true Track123456789
Grid results B
LotNumberExpiryDate Quantity
7892013-12-20 00:00:00.0001.55000
4562014-10-15 00:00:00.0002.45000
9992014-12-15 00:00:00.0009.99000
12013-12-20 00:00:00.0003.30000
42016-10-15 00:00:00.0004.40000
My intention is to insert the Grid results A into a table that has a primary key, thus RowID 1 will map to SalesOrderID=1 for example. ( that is why I have the rowID col to get into a loop processing one SalesOrder row at a time).
For each SalesOrderID row, I want to find the relevant Lots records in the xml and associate the SalesOrderID to them.
I am struggling with how to shred the xml given SalesOrderID=1 to find its relevant lots.
In the xml, the relevant lots can be identified with the salesOrderNumber,ItemId combination. I don't know how to extract just the xml for that combination.
Any ideas will be greatly appreciated.
Thank you very much.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

I'm not sure if this is exactly what you're looking for in the last query or not but give it a try:

SELECT doc1.col1.value('SalesOrderNumber[1]', 'varchar(50)') SalesOrderNumber

,doc.col.value('LotNumber[1]', 'varchar(50)') LotNumber

,doc.col.value('ExpiryDate[1]', 'datetime') ExpiryDate

,doc.col.value('Quantity[1]', 'decimal(13,5)') Quantity

FROM @xml.nodes('/ShipmentConfirmationMessage/ShippedItems/ShipmentConfirmationLine/Lots/Lot') doc(col)

CROSS APPLY @xml.nodes('/ShipmentConfirmationMessage/ShippedItems/ShipmentConfirmationLine') doc1(col1)

ORDER BY SalesOrderNumber, LotNumber

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Coriolan SSCommitted Points: 1931 More actions · Answer 2

That is exactly what I am looking for. Many thanks to you, Dwain.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

You're most welcome and thanks for letting me know it resolved your question.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Coriolan (7/12/2013)
That is exactly what I am looking for. Many thanks to you, Dwain.

Just be careful now, there is a mistake in that code that means you get every lot for every order.

This will give you the correct lots for each order and should perform a bit quicker due to the use of /text() in the .value expressions.

(for reference see the section titled "Data(), text(), and string() Accessors" at this url : http://msdn.microsoft.com/en-us/library/ms345118(v=sql.90).aspx)

SELECT doc.col.value('(SalesOrderNumber/text())[1]', 'varchar(50)') SalesOrderNumber

,doc1.col1.value('(LotNumber/text())[1]', 'varchar(50)') LotNumber

,doc1.col1.value('(ExpiryDate/text())[1]', 'datetime') ExpiryDate

,doc1.col1.value('(Quantity/text())[1]', 'decimal(13,5)') Quantity

FROM @xml.nodes('/ShipmentConfirmationMessage/ShippedItems/ShipmentConfirmationLine') doc(col)

CROSS APPLY col.nodes('Lots/Lot') doc1(col1)

ORDER BY SalesOrderNumber, LotNumber

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Coriolan SSCommitted Points: 1931 More actions · Answer 5

Thank you for noticing. I made the correction per recommendation and things are looking good.