title: How to query values from xml nodes?
body: <p>i have a table that contains an XML column:</p>
  
  <pre><code>CREATE TABLE Batches( 
     BatchID int,
     RawXml xml 
  )
  </code></pre>
  
  <p>The xml contains items such as:</p>
  
  <pre><code>&lt;GrobReportXmlFileXmlFile&gt;
     &lt;GrobReport&gt;
         &lt;ReportHeader&gt;
            &lt;OrganizationReportReferenceIdentifier&gt;1&lt;/OrganizationReportReferenceIdentifier&gt;
            &lt;OrganizationNumber&gt;4&lt;/OrganizationNumber&gt;
         &lt;/ReportHeader&gt;
    &lt;/GrobReport&gt;
     &lt;GrobReport&gt;
         &lt;ReportHeader&gt;
            &lt;OrganizationReportReferenceIdentifier&gt;2&lt;/OrganizationReportReferenceIdentifier&gt;
            &lt;OrganizationNumber&gt;4&lt;/OrganizationNumber&gt;
         &lt;/ReportHeader&gt;
    &lt;/GrobReport&gt;
     &lt;GrobReport&gt;
         &lt;ReportHeader&gt;
            &lt;OrganizationReportReferenceIdentifier&gt;3&lt;/OrganizationReportReferenceIdentifier&gt;
            &lt;OrganizationNumber&gt;4&lt;/OrganizationNumber&gt;
         &lt;/ReportHeader&gt;
    &lt;/GrobReport&gt;
     &lt;GrobReport&gt;
         &lt;ReportHeader&gt;
            &lt;OrganizationReportReferenceIdentifier&gt;4&lt;/OrganizationReportReferenceIdentifier&gt;
            &lt;OrganizationNumber&gt;4&lt;/OrganizationNumber&gt;
         &lt;/ReportHeader&gt;
    &lt;/GrobReport&gt;
  </code></pre>
  
  <p></p>
  
  <p>What i want is to generate a set, that contains:</p>
  
  <pre><code>OrganizationReportReferenceNumber  OrganizationNumber
  =================================  ==================
  1                                  4
  2                                  4
  3                                  4
  4                                  4
  </code></pre>
  
  <p>i've tried:</p>
  
  <pre><code>SELECT 
      foo.value('/ReportHeader/OrganizationReportReferenceIdentifier') AS ReportIdentifierNumber,
      foo.value('/ReportHeader/OrganizationNumber') AS OrginazationNumber
  FROM CDRBatches.RawXML.query('/GrobReportXmlFileXmlFile/GrobReport/ReportHeader') foo
  </code></pre>
  
  <p>but that doesn't work. i've tried:</p>
  
  <pre><code>SELECT 
      foo.value('/ReportHeader/OrganizationReportReferenceIdentifier') AS ReportIdentifierNumber,
      foo.value('/ReportHeader/OrganizationNumber') AS OrginazationNumber
  FROM RawXML.nodes('/GrobReportXmlFileXmlFile/GrobReport/ReportHeader') bar(foo)
  </code></pre>
  
  <p>But that doesn't work. The <strong>XPath</strong> expression</p>
  
  <pre><code>/GrobReportXmlFileXmlFile/GrobReport/ReportHeader
  </code></pre>
  
  <p>is correct; in any other xml system it returns:</p>
  
  <pre><code>&lt;ReportHeader&gt;
      &lt;OrganizationReportReferenceIdentifier&gt;1&lt;/OrganizationReportReferenceIdentifier&gt;
      &lt;OrganizationNumber&gt;4&lt;/OrganizationNumber&gt;
  &lt;/ReportHeader&gt;
  &lt;ReportHeader&gt;
      &lt;OrganizationReportReferenceIdentifier&gt;2&lt;/OrganizationReportReferenceIdentifier&gt;
      &lt;OrganizationNumber&gt;4&lt;/OrganizationNumber&gt;
  &lt;/ReportHeader&gt;
  &lt;ReportHeader&gt;
      &lt;OrganizationReportReferenceIdentifier&gt;3&lt;/OrganizationReportReferenceIdentifier&gt;
      &lt;OrganizationNumber&gt;4&lt;/OrganizationNumber&gt;
  &lt;/ReportHeader&gt;
  &lt;ReportHeader&gt;
      &lt;OrganizationReportReferenceIdentifier&gt;4&lt;/OrganizationReportReferenceIdentifier&gt;
      &lt;OrganizationNumber&gt;4&lt;/OrganizationNumber&gt;
  &lt;/ReportHeader&gt;
  </code></pre>
  
  <p>So, it's obvious from my queries that i'd <em>like</em> to see. After reading a dozen Stackover questions and answers, i'm no closer to solving the problem.</p>