Parsing XML by OpenXML with multiple Parent nodes with multiple child nodes
<p>I have the following XML:</p>

<pre><code>&lt;Report&gt;
    &lt;Accounts&gt;
        &lt;Account&gt;
          &lt;Currency&gt;USD&lt;/Currency&gt;
          &lt;AccountBalance&gt;45555&lt;/AccountBalance&gt;
          &lt;Payments&gt;
            &lt;PaymentData&gt;
              &lt;PaymentCode&gt;502&lt;/PaymentCode&gt;
              &lt;PaymentAmount currCode="GBP"&gt;7000.00000000&lt;/PaymentAmount&gt;
            &lt;/PaymentData&gt;
            &lt;PaymentData&gt;
              &lt;PaymentCode&gt;501&lt;/PaymentCode&gt;
              &lt;PaymentAmount currCode="USD"&gt;5000.00000000&lt;/PaymentAmount&gt;
            &lt;/PaymentData&gt;
          &lt;/Payments&gt;
        &lt;/Account&gt;
        &lt;Account&gt;
          &lt;Currency&gt;USD&lt;/Currency&gt;
          &lt;AccountBalance&gt;50000&lt;/AccountBalance&gt;
          &lt;Payments&gt;
            &lt;PaymentData&gt;
              &lt;PaymentCode&gt;501&lt;/PaymentCode&gt;
              &lt;PaymentAmount currCode="USD"&gt;5000.00000000&lt;/PaymentAmount&gt;
            &lt;/PaymentData&gt;
          &lt;/Payments&gt;
        &lt;/Account&gt;
    &lt;/Accounts&gt;
&lt;/Report&gt;
</code></pre>

<p>My SQL Code is parsing this with the following code:</p>

<pre><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'
            )
</code></pre>

<p>I am getting the following result:</p>

<pre><code>currCode | AccountBalance | PaymentCode | PaymentCurrCode | PaymentAmount
————————————————————————————————————————————————————————————————————————————————
USD      | 45555          | 502         |   GBP           |7000.00000000
USD      | 50000          | 501         |   USD           |5000.00000000
</code></pre>

<p>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:</p>

<pre><code>currCode | AccountBalance | PaymentCode | PaymentCurrCode | PaymentAmount
————————————————————————————————————————————————————————————————————————————————
USD      | 45555          | 502         |   GBP           |7000.00000000
USD      | 45555          | 501         |   USD           |5000.00000000
USD      | 50000          | 501         |   USD           |5000.00000000
</code></pre>