June 28, 2013 at 8:35 pm
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.
July 12, 2013 at 2:09 am
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 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
July 12, 2013 at 7:28 pm
That is exactly what I am looking for. Many thanks to you, Dwain.
July 12, 2013 at 8:30 pm
You're most welcome and thanks for letting me know it resolved your question.
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
July 13, 2013 at 5:22 pm
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);July 15, 2013 at 2:18 pm
Thank you for noticing. I made the correction per recommendation and things are looking good.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply