title: SQL Server Xml query with multiple namespaces
body: <p>I have a table in SQL server that contains an Xml column and I am having trouble querying it. I don't know enough about XPath to determine if my query is wrong, or if it is because of what seems like conflicting namespaces. Here is an example xml:</p>
  
  <pre><code>&lt;s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope"
              xmlns:a="http://www.w3.org/2005/08/addressing" 
              xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"&gt;
      &lt;s:Header&gt;
          &lt;!-- snip --&gt;
      &lt;/s:Header&gt;
      &lt;s:Body&gt;
          &lt;FetchRequest xmlns="http://www.foobar.org/my/schema"&gt;
              &lt;Contract xmlns:a="http://www.foobar.org/2014/04/datacontracts"
                        xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;
                  &lt;a:RequestedBy&gt;John Doe&lt;/a:RequestedBy&gt;
                  &lt;a:TransactionId&gt;ABC20140402000201&lt;/a:TransactionId&gt;
              &lt;/Contract&gt;
          &lt;/FetchRequest&gt;
      &lt;/s:Body&gt;
  &lt;/s:Envelope&gt;
  </code></pre>
  
  <p>I want to retrieve TransactionId from the xml. The query I tried was this:</p>
  
  <pre><code>SELECT TOP 100
  MessageXml,
  MessageXml.value('
      declare namespace s="http://www.w3.org/2003/05/soap-envelope";
      declare namespace a="http://www.w3.org/2005/08/addressing";
      (/s:Envelope/s:Body/FetchRequest/Contract/a:TransactionId)[1]', 'varchar(max)')
  FROM dbo.Message
  </code></pre>
  
  <p>I am getting back NULL for my MessageXml.value. If I remove everything after s:Body I seem to get a bunch of text that is concatenated, but as soon as I add FetchRequest I get NULL back in my results. </p>
  
  <p>I did notice that the Contract element defines a namespace of a, and the Envelope also defines a namespace of a, but I wasn't sure if that is a problem or not. </p>
  
  <p>How can I retrieve TransactionId using an XPath query given the above xml example?</p>