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

Shredding xml ( parent child relation) Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 8:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 8:22 PM
Points: 133, Visits: 323
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.
Post #1468760
Posted Friday, July 12, 2013 2:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:45 PM
Points: 3,617, Visits: 5,237
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!
Post #1472896
Posted Friday, July 12, 2013 7:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 8:22 PM
Points: 133, Visits: 323
That is exactly what I am looking for. Many thanks to you, Dwain.
Post #1473274
Posted Friday, July 12, 2013 8:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:45 PM
Points: 3,617, Visits: 5,237
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!
Post #1473275
Posted Saturday, July 13, 2013 5:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:19 PM
Points: 1,786, Visits: 5,682
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1473329
    Posted Monday, July 15, 2013 2:18 PM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Tuesday, July 1, 2014 8:22 PM
    Points: 133, Visits: 323
    Thank you for noticing. I made the correction per recommendation and things are looking good.

    Post #1473893
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse