Parsing XML by OpenXML with multiple Parent nodes with multiple child nodes

Question

I have the following XML:

<Report>
    <Accounts>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>45555</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>502</PaymentCode>
              <PaymentAmount currCode="GBP">7000.00000000</PaymentAmount>
            </PaymentData>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>50000</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
    </Accounts>
</Report>

My SQL Code is parsing this with the following code:

SELECT  
            [currCode]  AS [Currency], 
            [AccountBalance] AS [AccountBalance],
            [PaymentCode] AS [PaymentCode],
            [PaymentCurrCode] AS [PaymentCurrCode],
            [PaymentAmount] AS [PaymentAmount]
        FROM OPENXML(@hDoc, 'Report/Accounts/Account',2)
            WITH 
            (
                [currCode] [nchar](3) 'currCode',
                [AccountBalance] [decimal](18, 0) 'AccountBalance',

                [PaymentCode] [nchar](10) 'Payments/PaymentData/PaymentCode',
                [PaymentCurrCode] [nchar](3) 'Payments/PaymentData/PaymentAmount/@currCode',
                [PaymentAmount] [decimal](18, 0) 'Payments/PaymentData/PaymentAmount'
            )

I am getting the following result:

currCode | AccountBalance | PaymentCode | PaymentCurrCode | PaymentAmount
————————————————————————————————————————————————————————————————————————————————
USD      | 45555          | 502         |   GBP           |7000.00000000
USD      | 50000          | 501         |   USD           |5000.00000000

I am trying to get the multiple paymentdata and multiple account with the same openXml query. How Can is get all the data with the following result:

currCode | AccountBalance | PaymentCode | PaymentCurrCode | PaymentAmount
————————————————————————————————————————————————————————————————————————————————
USD      | 45555          | 502         |   GBP           |7000.00000000
USD      | 45555          | 501         |   USD           |5000.00000000
USD      | 50000          | 501         |   USD           |5000.00000000

Answer 1

This is an up-to-date and state-of-the-art approach with XQuery/XPath methods. The result is the same, just faster and better to read:

DECLARE @XML XML=
'<Report>
    <Accounts>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>45555</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>502</PaymentCode>
              <PaymentAmount currCode="GBP">7000.00000000</PaymentAmount>
            </PaymentData>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>50000</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
    </Accounts>
</Report>';

SELECT Payment.value('(../../Currency)[1]','nchar(3)') AS currCode
      ,Payment.value('(../../AccountBalance)[1]','decimal(18,0)') AS AccountBalance
      ,Payment.value('PaymentCode[1]','nchar(10)') AS PaymentCode
      ,Payment.value('PaymentAmount[1]/@currCode','nchar(3)') AS PaymentCurrCode
      ,Payment.value('PaymentAmount[1]','decimal(18,0)') AS PaymentCurrCode
FROM @XML.nodes('Report/Accounts/Account/Payments/PaymentData') AS One(Payment)

Answer 2

This should work for you:

DECLARE @XML XML=
'<Report>
    <Accounts>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>45555</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>502</PaymentCode>
              <PaymentAmount currCode="GBP">7000.00000000</PaymentAmount>
            </PaymentData>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>50000</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
    </Accounts>
</Report>';

DECLARE @hDoc INT;
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML; 

SELECT  
            [currCode]  AS [Currency], 
            [AccountBalance] AS [AccountBalance],
            [PaymentCode] AS [PaymentCode],
            [PaymentCurrCode] AS [PaymentCurrCode],
            [PaymentAmount] AS [PaymentAmount]
        FROM OPENXML(@hDoc, 'Report/Accounts/Account/Payments/PaymentData',2)
            WITH 
            (
                [currCode] [nchar](3) '../../Currency',
                [AccountBalance] [decimal](18, 0) '../../AccountBalance',

                [PaymentCode] [nchar](10) 'PaymentCode',
                [PaymentCurrCode] [nchar](3) 'PaymentAmount/@currCode',
                [PaymentAmount] [decimal](18, 0) 'PaymentAmount'
            )
EXEC sp_xml_removedocument @hDoc;