control_sql_-_shipment_items [Support]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======  ======



===== 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