Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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's line items into a table.
 +
 +
 +
 +===== 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('%,%', T.LineItemID)-2 ) AS INT ) LineItemID,
 +       Description,
 +       LineItemText,
 +       Quantity, 
 +       IsValidTax, 
 +       TaxesCalcPrice, 
 +       TaxesOverriddenPrice, 
 +       TaxesIsOverridden, 
 +       TaxItems
 +FROM ( SELECT ShipmentXML.ID,
 +              ShipmentItem.value('(LineItemID/text())[1]', 'NVarChar(MAX)') AS LineItemID,
 +              ShipmentItem.value('(Quantity/text())[1]', 'FLOAT') AS Quantity,
 +              ShipmentItem.value('(Description/text())[1]', 'NVarChar(Max)') AS Description,
 +              ShipmentItem.value('(LineItemText/text())[1]', 'NVarChar(Max)') AS LineItemText,
 +              ShipmentItem.value('(IsValidTax/text())[1]', 'BIT') AS IsValidTax,
 +              ShipmentItem.value('(TaxesCalcPrice/text())[1]', 'FLOAT') AS TaxesCalcPrice,
 +              ShipmentItem.value('(TaxesOverriddenPrice/text())[1]', 'FLOAT') AS TaxesOverriddenPrice,
 +              ShipmentItem.value('(TaxesIsOverridden/text())[1]', 'BIT') AS TaxesIsOverridden,
 +              ShipmentItem.query('(node()[local-name() = "TaxItems"]/Taxes)') TaxItems
 +       FROM (SELECT *, CAST(ShipLineItemsXML AS XML) ItemsXML 
 +             FROM Shipments
 +             WHERE Shipments.ID = @ShipmentID
 +             ) ShipmentXML
 +                    CROSS APPLY ItemsXML.nodes('//LineItem') AS VARIABLES(ShipmentItem) 
 +       ) T
 +</code>
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 09/23/2011
 +  * Version : 01
 +
 +
 +