Differences
This shows you the differences between two versions of the page.
control_sql_-_shipment_items [2019/01/27 11:29] |
control_sql_-_shipment_items [2019/01/27 11:29] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== | ||
+ | |||
+ | |||
+ | |||
+ | ===== Explanation of SQL ===== | ||
+ | |||
+ | |||
+ | |||
+ | This query converts the XML of a shipment' | ||
+ | |||
+ | |||
+ | |||
+ | ===== Risk of Data Corruption if Run Improperly ===== | ||
+ | |||
+ | **None**. This is a selection query and no data is modified in the running of it. | ||
+ | |||
+ | |||
+ | |||
+ | ===== SQL ===== | ||
+ | |||
+ | |||
+ | |||
+ | Replace the ID in the second line with the ID of the Shipment you are querying. | ||
+ | |||
+ | |||
+ | |||
+ | <code sql> | ||
+ | DECLARE @ShipmentID INT; | ||
+ | SET @ShipmentID = 1002; | ||
+ | SELECT @ShipmentID as ShipmentID, | ||
+ | CAST( SUBSTRING( LineItemID, 2, PATINDEX(' | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | FROM ( SELECT ShipmentXML.ID, | ||
+ | ShipmentItem.value(' | ||
+ | ShipmentItem.value(' | ||
+ | ShipmentItem.value(' | ||
+ | ShipmentItem.value(' | ||
+ | ShipmentItem.value(' | ||
+ | ShipmentItem.value(' | ||
+ | ShipmentItem.value(' | ||
+ | ShipmentItem.value(' | ||
+ | ShipmentItem.query(' | ||
+ | FROM (SELECT *, CAST(ShipLineItemsXML AS XML) ItemsXML | ||
+ | FROM Shipments | ||
+ | WHERE Shipments.ID = @ShipmentID | ||
+ | ) ShipmentXML | ||
+ | CROSS APPLY ItemsXML.nodes('// | ||
+ | ) T | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Version Information ===== | ||
+ | * Entered : 09/23/2011 | ||
+ | * Version : 01 | ||
+ | |||
+ | |||
+ | |||