In TSQL, what is the best way to iterate through 1..* child nodes of XML data and retreive values?

Question

I have a simple XML structure:

<Receipt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ReceiptID="0" UserID="0" UserCardID="0" PaymentMethodID="1" MerchantID="0" MerchantTaxID="MERCHANT_TAX_ID" MerchantReceiptID="MERCHANT_RECEIPT_ID" MerchantReceiptReferenceID="MERCHANT_RECEIPT_REF_ID" ReceiptTypeID="0" TransactionTypeID="2" MerchantReceiptDate="2009-12-10T18:01:14.2101141-07:00" Tax1PerCent="0" Tax2PerCent="0" Tax3PerCent="0" Tax1Total="0" Tax2Total="0" Tax3Total="0" TotalTax="5" Subtotal="100" ReceiptTotal="105" DateAdded="2009-12-10T18:01:14.2101141-07:00" MerchantStore="MERCHANT_STORE_NAME" StoreAddress1="228127 Long Street" StoreAddress2="" StoreCity="Los Angeles" StoreState="California" StoreZip="90212" StoreCountry="USA" StorePhone1="310-444-3333" StorePhone2="" StoreFax="310-333-2222" ReceiptHeader1="Test Receipt Header 1" ReceiptHeader2="Header 2" ReceiptHeader3="Header 3" ReceiptFooter1="Test Receipt Footer 1" ReceiptFooter2="Footer 2" ReceiptFooter3="Footer 3" ReceiptCreditCost="6" UserPaidForReceipt="false">
  <Errors />
  <ReceiptItem LineItemID="0" UserID="0" ReceiptID="0" MerchantItemID="111xxxTEST_ITEM_1" LineItemTypeID="1" ItemDesc1="Item 1 - Desc1: This is a test item purchased on a test receipt, line 1" ItemDesc2="Item 1 - Desc2: Item description, line 2" ItemDesc3="Item 1 - Desc3: Item description, line 3" Quantity="1" PricePerItem="50" LineItemTotal="50" DateAdded="2009-12-10T18:01:14.2101141-07:00">
    <Errors />
    <LineItemType LineItemTypeID="1" LineItemType="Purchase">
      <Errors />
    </LineItemType>
  </ReceiptItem>
  <ReceiptItem LineItemID="0" UserID="0" ReceiptID="0" MerchantItemID="111xxxTEST_ITEM_2" LineItemTypeID="1" ItemDesc1="Item 2 - Desc1: This is a test item purchased on a test receipt, line 1" ItemDesc2="Item 2 - Desc2: Item description, line 2" ItemDesc3="Item 2 - Desc3: Item description, line 3" Quantity="1" PricePerItem="25" LineItemTotal="25" DateAdded="2009-12-10T18:01:14.2101141-07:00">
    <Errors />
    <LineItemType LineItemTypeID="1" LineItemType="Purchase">
      <Errors />
    </LineItemType>
  </ReceiptItem>
.
.
.

I'm sending the serialized stream to my sproc where I can grab individual values of what I need, like IDs, etc. However I want to iterate through all ReceiptItems, grabbing values and saving them to an ReceiptItems table.

Is there a simple way of creating a while loop to accomplish this?

Thank you.

Answer 1

OMG Ponies - great lead, which brought me to the solution... thank you:

Using a Common Table Expression, I was able to iterate through the child nodes I needed for the following data:

    SET @TestData =
'<Receipt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ReceiptID="0" UserID="0" UserCardID="0" PaymentMethodID="1" MerchantID="0" MerchantTaxID="MERCHANT_TAX_ID" MerchantReceiptID="MERCHANT_RECEIPT_ID" MerchantReceiptReferenceID="MERCHANT_RECEIPT_REF_ID" ReceiptTypeID="0" TransactionTypeID="2" MerchantReceiptDate="2009-12-10T18:01:14.2101141-07:00" Tax1PerCent="0" Tax2PerCent="0" Tax3PerCent="0" Tax1Total="0" Tax2Total="0" Tax3Total="0" TotalTax="5" Subtotal="100" ReceiptTotal="105" DateAdded="2009-12-10T18:01:14.2101141-07:00" MerchantStore="MERCHANT_STORE_NAME" StoreAddress1="228127 Long Street" StoreAddress2="" StoreCity="Los Angeles" StoreState="California" StoreZip="90212" StoreCountry="USA" StorePhone1="310-444-3333" StorePhone2="" StoreFax="310-333-2222" ReceiptHeader1="Test Receipt Header 1" ReceiptHeader2="Header 2" ReceiptHeader3="Header 3" ReceiptFooter1="Test Receipt Footer 1" ReceiptFooter2="Footer 2" ReceiptFooter3="Footer 3" ReceiptCreditCost="1" UserPaidForReceipt="false">
  <Errors />
  <ReceiptItem LineItemID="0" UserID="0" ReceiptID="0" MerchantItemID="111xxxTEST_ITEM_1" LineItemTypeID="1" ItemDesc1="Item 1 - Desc1: This is a test item purchased on a test receipt, line 1" ItemDesc2="Item 1 - Desc2: Item description, line 2" ItemDesc3="Item 1 - Desc3: Item description, line 3" Quantity="1" PricePerItem="50" LineItemTotal="50" DateAdded="2009-12-10T18:01:14.2101141-07:00">
    <Errors />
    <LineItemType LineItemTypeID="1" LineItemType="Purchase">
      <Errors />
    </LineItemType>
  </ReceiptItem>
  <ReceiptItem LineItemID="0" UserID="0" ReceiptID="0" MerchantItemID="111xxxTEST_ITEM_2" LineItemTypeID="1" ItemDesc1="Item 2 - Desc1: This is a test item purchased on a test receipt, line 1" ItemDesc2="Item 2 - Desc2: Item description, line 2" ItemDesc3="Item 2 - Desc3: Item description, line 3" Quantity="1" PricePerItem="25" LineItemTotal="25" DateAdded="2009-12-10T18:01:14.2101141-07:00">
    <Errors />
    <LineItemType LineItemTypeID="1" LineItemType="Purchase">
      <Errors />
    </LineItemType>
  </ReceiptItem>
.
.
.

Given that data, I wrote the following code to retrieve all the values I needed, without really having to explicit node traversing:

-- now iterate through the receipt items and  with a CTE add them to to tbl_ReceiptLineItems
;WITH CTE_ITEMS (Receipt, MerchantItemID, LineItemTypeID,
    ItemDesc1, ItemDesc2, ItemDec3, ItemNumber, Quantity,
    PricePerItem, LineItemTotal, DateAdded)
AS
(
 -- we're only interested in the receipt line items
 -- AS we've already retrieved the receipt top-level items
 SELECT 
  VIRT.node.query('./*') AS Receipt,
  --@UserID AS UserID,
  --@ReceiptID AS REceipt,
  VIRT.node.value('@MerchantItemID', 'varchar(50)') AS MerchantItemID,
  VIRT.node.value('@LineItemTypeID', 'int') AS LineItemTypeID,
  VIRT.node.value('@ItemDesc1', 'varchar(128)') AS ItemDesc1,
  VIRT.node.value('@ItemDesc2', 'varchar(128)') AS ItemDesc2,
  VIRT.node.value('@ItemDesc3', 'varchar(128)') AS ItemDesc3,
  VIRT.node.value('@ItemNumber', 'varchar(128)') AS ItemNumber,
  VIRT.node.value('@Quantity', 'int') AS Quantity,
  VIRT.node.value('@PricePerItem', 'decimal(18,2)') AS PricePerItem,
  VIRT.node.value('@LineItemTotal', 'decimal(18,2)') AS LineItemTotal,
  VIRT.node.value('@DateAdded', 'datetime') AS DateAded
 FROM @TestData.nodes('/Receipt/ReceiptItem') AS VIRT(node)

 UNION ALL

 SELECT 
  VIRT.node.query('./*') AS Receipt,
  --@UserID AS UserID,
  --@ReceiptID AS REceipt,
  VIRT.node.value('@MerchantItemID', 'varchar(50)') AS MerchantItemID,
  VIRT.node.value('@LineItemTypeID', 'int') AS LineItemTypeID,
  VIRT.node.value('@ItemDesc1', 'varchar(128)') AS ItemDesc1,
  VIRT.node.value('@ItemDesc2', 'varchar(128)') AS ItemDesc2,
  VIRT.node.value('@ItemDesc3', 'varchar(128)') AS ItemDesc3,
  VIRT.node.value('@ItemNumber', 'varchar(128)') AS ItemNumber,
  VIRT.node.value('@Quantity', 'int') AS Quantity,
  VIRT.node.value('@PricePerItem', 'decimal(18,2)') AS PricePerItem,
  VIRT.node.value('@LineItemTotal', 'decimal(18,2)') AS LineItemTotal,
  VIRT.node.value('@DateAdded', 'datetime') AS DateAded
 FROM CTE_ITEMS CROSS APPLY Receipt.nodes('./ReceiptItem') AS VIRT(node)
)


SELECT @UserID AS UserID, @ReceiptID AS ReceiptID, -- Receipt,
  MerchantItemID, LineItemTypeID,
  ItemDesc1, ItemDesc2, ItemDec3, ItemNumber, Quantity,
  PricePerItem, LineItemTotal, DateAdded

FROM CTE_ITEMS