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] (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
 +
 +
 +