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


Shredding xml ( parent child relation)


Shredding xml ( parent child relation)

Author
Message
Coriolan
Coriolan
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 349
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
RowID SalesOrderNumber ItemId IsBackOrdered TrackingNumber
1 SalesOrder1 10982 false Track1234
2 SalesOrder2 10983 true Track123456789

Grid results B
LotNumber ExpiryDate Quantity
789 2013-12-20 00:00:00.000 1.55000
456 2014-10-15 00:00:00.000 2.45000
999 2014-12-15 00:00:00.000 9.99000
1 2013-12-20 00:00:00.000 3.30000
4 2016-10-15 00:00:00.000 4.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.
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Coriolan
Coriolan
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 349
That is exactly what I am looking for. Many thanks to you, Dwain.
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
You're most welcome and thanks for letting me know it resolved your question.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2270 Visits: 7827
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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Coriolan
    Coriolan
    SSC-Enthusiastic
    SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

    Group: General Forum Members
    Points: 137 Visits: 349
    Thank you for noticing. I made the correction per recommendation and things are looking good.
    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