title: xQuery, getting the value with namespaces?
body: <p>I am just starting with xquery and currently I don't know, why the following is not working. I've created two XML files, they are identically, except that one has namespaces and the other doesn't:</p>
  
  <pre><code>&lt;Envelope&gt;
      &lt;Header&gt;
          &lt;JustAValue&gt;12345&lt;/JustAValue&gt;
      &lt;/Header&gt;
      &lt;Body&gt;
          &lt;someBody&gt;
              &lt;ValueIWant&gt;12345678910&lt;/ValueIWant&gt;
          &lt;/someBody&gt;
      &lt;/Body&gt;
  &lt;/Envelope&gt;
  
  
  &lt;env:Envelope xmlns:env='http://schemas.xmlsoap.org/soap/envelope/'&gt;
      &lt;env:Header xmlns:wsa='http://www.w3.org/2005/08/addressing'&gt;
          &lt;JustAValue&gt;12345&lt;/JustAValue&gt;
      &lt;/env:Header&gt;
      &lt;env:Body xmlns:wsu='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd' wsu:Id='element-28-1344266615792-223644235'&gt;
          &lt;ns1:someBody xmlns='http://someURL.com' xmlns:ns1='http://someURL.com'&gt;
              &lt;ValueIWant&gt;12345678910&lt;/ValueIWant&gt;
          &lt;/ns1:someBody&gt;
      &lt;/env:Body&gt;
  &lt;/env:Envelope&gt;
  </code></pre>
  
  <p>Both xml files are in a xml field in the database.</p>
  
  <p>Now I try to get the "ValueIWant" and I do it like this:</p>
  
  <pre><code>DECLARE @xml xml
  
  Select @xml = completexml from MyDB.dbo.Xml where PKxml = 3 -- XML without namespaces
  
  declare @somevalue nvarchar(max)
  
  set @somevalue = @xml.value('(/Envelope/Body/someBody/ValueIWant/text())[1]', 'nvarchar(max)');
  
  select @somevalue -- -&gt; 12345678910
  
  
  Select @xml = completexml from MyDB.dbo.Xml where PKxml = 2 -- XML with namespaces
  
  set @somevalue = @xml.value('
  declare namespace env="http://schemas.xmlsoap.org/soap/envelope/"; 
  declare namespace ns1="http://someURL.com"; 
  (/env:Envelope/env:Body/ns1:someBody/ValueIWant/text())[1]', 'nvarchar(max)');
  
  select @somevalue -- -&gt; NULL
  </code></pre>
  
  <p><strong>What am I doing wrong here?</strong></p>
  
  <p>I've played around a bit with the XML:</p>
  
  <pre><code>&lt;env:Envelope xmlns:env='http://schemas.xmlsoap.org/soap/envelope/'&gt;
      &lt;env:Header xmlns:wsa='http://www.w3.org/2005/08/addressing'&gt;
          &lt;JustAValue&gt;12345&lt;/JustAValue&gt;
      &lt;/env:Header&gt;
      &lt;env:Body xmlns:wsu='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd' wsu:Id='element-28-1344266615792-223644235'&gt;
          &lt;ns1:someBody xmlns='http://someURL.com' xmlns:ns1='http://someURL.com'&gt;
              &lt;ValueIWant&gt;12345678910&lt;/ValueIWant&gt;
          &lt;/ns1:someBody&gt;
          &lt;BodyValue&gt;BodyValue&lt;/BodyValue&gt;
          &lt;env:BodyValue&gt;BodyValue&lt;/env:BodyValue&gt;
      &lt;/env:Body&gt;
      &lt;env:TestValue1&gt;TestValue&lt;/env:TestValue1&gt;
      &lt;TestValue2&gt;TestValue2&lt;/TestValue2&gt;
  &lt;/env:Envelope&gt;
  </code></pre>
  
  <p>I can get all the values: BodyValue, TestValue1 and TestValue2. No problem at all, but as soon as I want to change into the ns1 namespace and get ValueIWant, it just returns NULL.</p>