title: Parsing XML by OpenXML with multiple Parent nodes with multiple child nodes
body: <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>