This query converts the XML of a shipment's line items into a table.

None. This is a selection query and no data is modified in the running of it.

Replace the ID in the second line with the ID of the Shipment you are querying.

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
  • Entered : 09/23/2011
  • Version : 01
You could leave a comment if you were logged in.