title: Extracting XML data in SQL - too many cross apply statements
body: <p>I have an xml document containing details from a Statement:</p>
  
  <pre><code>&lt;Statement&gt;
  &lt;Id /&gt;
  
  &lt;Invoices&gt;
      &lt;Invoice&gt;
          &lt;Id /&gt;
          &lt;Date /&gt;
          &lt;AmountDue /&gt;
          etc.
      &lt;/Invoice&gt;
  
      &lt;Invoice&gt;
          &lt;Id /&gt;
          &lt;Date /&gt;
          &lt;AmountDue /&gt;
          etc.
      &lt;/Invoice&gt;
  
      &lt;Invoice&gt;
          &lt;Id /&gt;
          &lt;Date /&gt;
          &lt;AmountDue /&gt;
          etc.
      &lt;/Invoice&gt;
  &lt;/Invoices&gt;
  
  &lt;/Statement&gt;
  </code></pre>
  
  <p>This works fine for the Statement specific details:</p>
  
  <pre><code>SET @statementId = @xml.value('(Id)[1]', 'UNIQUEIDENTIFIER');
  </code></pre>
  
  <p>but it requires a singleton, and only returns the first value.  I need ALL of the values for the invoices, not just the first so a singleton won't work.</p>
  
  <p>I am able to get the information out using cross apply statements like this:</p>
  
  <pre><code>SELECT 
  @statementId AS STATEMENT_ID
  Id.value('.', 'uniqueidentifier') AS INVOICE_ID
  Date.value('.', 'smalldatetime') AS INVOICE_DATE
  Due.value('.', 'decimal') AS INVOICE_AMOUNT_DUE
  
  FROM @xml.nodes('Statement') A(S)
  cross apply S.nodes('Invoices/Invoice') B(InvoiceD)
  cross apply InvoiceD.nodes('Id') C(Id)
  cross apply InvoiceD.nodes('Date') D(Date)
  cross apply InvoiceD.nodes('AmountDue') E(Due)
  </code></pre>
  
  <p>This returns an Id, date, and amount from each Invoice in the Statement - perfect.</p>
  
  <p>My problem comes when I try to extract all of the invoice details.  I currently have seven cross apply statements and I got the following message:</p>
  
  <blockquote>
    <p>"The query processor ran out of internal resources and could not
    produce a query plan. This is a rare event and only expected for
    extremely complex queries or queries that reference a very large
    number of tables or partitions. Please simplify the query. If you
    believe you have received this message in error, contact Customer
    Support Services for more information."</p>
  </blockquote>
  
  <p>What I want to do is have one cross apply for the Invoice and narrow down the exact field in the select statement, but unless I use '.' I must make the statement return a singleton and I don't get all of the data that I need.</p>
  
  <p>I have done some research about specifying a namespace within the select statement, but all of the examples set the namespace to be an http address instead of a node in an xml document and I haven't gotten anything to return yet using this approach.</p>
  
  <p>The result I'm looking for is something like this, but with more Invoice Details:</p>
  
  <pre><code>STATEMENT_ID      INVOICE_ID      INVOICE_DATE      INVOICE_AMOUNT_DUE     ...
  Statement-1-Id    Invoice-1-Id    Invoice-1-Date    Invoice-1-AmountDue    ...
  Statement-1-Id    Invoice-2-Id    Invoice-2-Date    Invoice-2-AmountDue    ...
  Statement-1-Id    Invoice-3-Id    Invoice-3-Date    Invoice-3-AmountDue    ...
  </code></pre>
  
  <p>Where should I go from here?</p>
  
  <p>EDIT: I removed some unnecessary information.  Getting all of the invoice-specific details is my goal here.</p>