title: SQL server XML query with xquery
body: <p>I'm trying to reach &quot;row&quot; data below using xquery on sql server.</p>
  <pre><code>DECLARE @xmlData XML
  SET @xmlData = '
  &lt;Envelope xmlns:S=&quot;http://schemas.xmlsoap.org/soap/envelope/&quot;&gt;
    &lt;Header&gt;
      &lt;WorkContext xmlns=&quot;http://oracle.com/weblogic/soap/workarea/&quot;&gt;rO0ABXdbABh3ZWJsb2duNDAAAA==&lt;/WorkContext&gt;
    &lt;/Header&gt;
    &lt;Body&gt;
      &lt;getReport xmlns:ns0=&quot;http://report_xml.org&quot;&gt;
        &lt;return&gt;
          &lt;pagekey&gt;F1D96FB7ACFBA3BD5D5E77C5&lt;/pagekey&gt;
          &lt;rapor&gt;
            &lt;dataSet xmlns=&quot;http://report_xml.org/dataSet/201006&quot;&gt;
              &lt;dataTable&gt;
                &lt;id&gt;ABC1&lt;/id&gt;
                &lt;row&gt;
                  &lt;dim1&gt;Paris&lt;/dim1&gt;
                  &lt;dim2&gt;Societe&lt;/dim2&gt;
                  &lt;dim3&gt;Car&lt;/dim3&gt;
                &lt;/row&gt;
                &lt;row&gt;
                  &lt;dim1&gt;Paris&lt;/dim1&gt;
                  &lt;dim2&gt;Credit&lt;/dim2&gt;
                  &lt;dim3&gt;Car&lt;/dim3&gt;
                &lt;/row&gt;
              &lt;/dataTable&gt;
            &lt;/dataSet&gt;
          &lt;/rapor&gt;
        &lt;/return&gt;
      &lt;/getReport&gt;
    &lt;/Body&gt;
  &lt;/Envelope&gt;
  '
  </code></pre>
  <p>When I use below code I can get the the &quot;pagekey&quot; value</p>
  <pre><code>; WITH XMLNAMESPACES ('http://report_xml.org/dataSet/201006' AS DS)
  select @xmlData.query('/Envelope/Body/getReport/return/pagekey')
  </code></pre>
  <p>or I can reach the &quot;dataSet&quot; xml using this one</p>
  <pre><code>; WITH XMLNAMESPACES ('http://report_xml.org/dataSet/201006' AS DS)
  select @xmlData.query('/Envelope/Body/getReport/return/rapor/DS:dataSet')
  </code></pre>
  <p>However, when I try to reach further down like</p>
  <pre><code>; WITH XMLNAMESPACES ('http://report_xml.org/dataSet/201006' AS DS)
  select @xmlData.query('/Envelope/Body/getReport/return/rapor/DS:dataSet/dataTable/id')
  or
  select @xmlData.query('/Envelope/Body/getReport/return/rapor/DS:dataSet/dataTable/row')
  </code></pre>
  <p>Queries return empty.</p>
  <p>what am I missing?</p>