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