Something like this:

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>

</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);

What you need to look up is SQL Server XQuery, specifically the nodes() and value() functions. Beyond that, it's W3C XML standards, if you haven't gone through those (trust me, it's painful).

(Edit for readability and layout on the XML.)