title: Select XML element in SQL Server
body: <p>I have some XML that I need to parse using SQL Server 2008.  I think I'm close to getting what I want, but I don't have the correct syntax (I believe).</p>
  
  <p>I have the following:</p>
  
  <pre><code>DECLARE @doc XML
  SET @doc = '&lt;ROOT&gt;          
      &lt;InvoiceDetail&gt;
          &lt;OrderId&gt;1000000&lt;/OrderId&gt;
          &lt;OrderTypeId&gt;2&lt;/OrderTypeId&gt;
          &lt;Id&gt;2000&lt;/Id&gt;
          &lt;InvoiceItems&gt;
              &lt;InvoiceItem&gt;
                  &lt;LineId&gt;1&lt;/LineId&gt;
                  &lt;Cd&gt;123456&lt;/Cd&gt;
                  &lt;Description&gt;Item 1&lt;/Description&gt;
                  &lt;Quantity&gt;1&lt;/Quantity&gt;
                  &lt;UnitPrice&gt;99.990000&lt;/UnitPrice&gt;
              &lt;/InvoiceItem&gt;
              &lt;InvoiceItem&gt;
                  &lt;LineId&gt;2&lt;/LineId&gt;
                  &lt;Cd&gt;234567&lt;/Cd&gt;
                  &lt;Description&gt;Item 2&lt;/Description&gt;
                  &lt;Quantity&gt;1&lt;/Quantity&gt;
                  &lt;UnitPrice&gt;89.990000&lt;/UnitPrice&gt;
              &lt;/InvoiceItem&gt;
          &lt;/InvoiceItems&gt;
      &lt;/InvoiceDetail&gt;
      &lt;InvoiceDetail&gt;
          &lt;OrderId&gt;1200000&lt;/OrderId&gt;
          &lt;OrderTypeId&gt;1&lt;/OrderTypeId&gt;
          &lt;Id&gt;3000&lt;/Id&gt;
          &lt;InvoiceItems&gt;
              &lt;InvoiceItem&gt;
                  &lt;LineId&gt;1&lt;/LineId&gt;
                  &lt;Cd&gt;234567&lt;/Cd&gt;
                  &lt;Description&gt;Item 2&lt;/Description&gt;
                  &lt;Quantity&gt;1&lt;/Quantity&gt;
                  &lt;UnitPrice&gt;89.990000&lt;/UnitPrice&gt;
              &lt;/InvoiceItem&gt;
              &lt;InvoiceItem&gt;
                  &lt;LineId&gt;2&lt;/LineId&gt;
                  &lt;Cd&gt;345678&lt;/Cd&gt;
                  &lt;Description&gt;Item 3&lt;/Description&gt;
                  &lt;Quantity&gt;1&lt;/Quantity&gt;
                  &lt;UnitPrice&gt;79.990000&lt;/UnitPrice&gt;
              &lt;/InvoiceItem&gt;
          &lt;/InvoiceItems&gt;
      &lt;/InvoiceDetail&gt;
  &lt;/ROOT&gt;'
  
  SELECT 
        Invoices.Node.value('@OrderId', 'VARCHAR(10)') 'OrderID'
      , Invoices.Node.value('@Id', 'INT') 'InvoiceId'
      , Items.Cd.value('.', 'VARCHAR(14)') 'ItemId'
  FROM 
      @doc.nodes('//InvoiceDetail') Invoices(Node)
      CROSS APPLY Invoices.Node.nodes('./InvoiceItems/InvoiceItem/Cd') Items(Cd)
  </code></pre>
  
  <p>I get the following results:</p>
  
  <pre><code>NULL    NULL    123456
  NULL    NULL    234567
  NULL    NULL    234567
  NULL    NULL    345678
  </code></pre>
  
  <p>I'm trying to get the following:</p>
  
  <pre><code>1000000 2000    123456
  1000000 2000    234567
  1200000 3000    234567
  1200000 3000    345678
  </code></pre>
  
  <p>What am I doing wrong?</p>