You're welcome.

On the two rows (as you figured out), I only copied one into my test harness. You'll have two (or more) when you use real data.

However, you should be aware that the XML structure implies the possibility of more than one invoice per entry. It has "Invoices" as a tag with "Invoice" as a sub-set of it. Thus, you could end up with even more rows than you expect, if one or more of the entries have two or more invoices.

For example:

IF OBJECT_ID(N'tempdb..#T') IS NOT NULL

DROP TABLE #T;

CREATE TABLE #T

(ID INT IDENTITY

PRIMARY KEY,

XMLCol XML);

INSERT INTO #T

(XMLCol)

VALUES ('<?xml version="1.0"?>

<Payment MessageLabel="GP_PayInvoiceToAR">

<SubID>0</SubID>

<CCType>Visa</CCType>

<CCLast4Digits />

<CCExpirationDate>12/2024</CCExpirationDate>

<TransactionID>2152966835</TransactionID>

<PaymentAmount>2.06</PaymentAmount>

<PaymentDate>Aug 20 2012</PaymentDate>

<ReferenceNumber>23152</ReferenceNumber>

<Comment>INV00000149243</Comment>

<Invoices>

<Invoice>

<InvoiceNumber>INV00000149243</InvoiceNumber>

<InvoiceAmount>2.08</InvoiceAmount>

<ContractNumber />

<PaidThruDate>Jan 01 1900</PaidThruDate>

</Invoice>

<Invoice>

<InvoiceNumber>INV00000149244</InvoiceNumber>

<InvoiceAmount>2.08</InvoiceAmount>

<ContractNumber />

<PaidThruDate>Jan 01 1900</PaidThruDate>

</Invoice>

</Invoices>

</Payment>');

SELECT XMLCol.value('(/Payment/TransactionID/text())[1]', 'varchar(100)') AS TransactionID,

XMLCol.value('(/Payment/ReferenceNumber/text())[1]', 'varchar(100)') AS ReferenceNumber,

Payment.Invoice.query('.').value('(/Invoice/InvoiceNumber/text())[1]', 'varchar(100)') AS InvoiceNumber

FROM #T AS T

CROSS APPLY XMLCol.nodes('Payment/Invoices/Invoice') AS Payment (Invoice);

All I did was add another invoice (I added 1 to the invoice number for it), and you'll now get two rows from the query, one for each invoice. The TransactionID and ReferenceNumber columns will have the same data in both rows, but you'll get both invoice numbers (1 per row).

Make sure that either the data doesn't contain anything like that, and never will, or that your code can deal with that correctly.

Make sense?