title: Trouble using namespace in SQL Server XML Data Type query
body: <p>For background see this question:</p>
  
  <p><a href="https://stackoverflow.com/questions/1415003/sql-server-xml-data-type-query-issue">SQL Server XML Data Type query issue</a></p>
  
  <p>I'm trying to query against an XML object in SQL Server 2005. The query works fine when there's no namespace defined in the XML. However, when the namespace element is there I cannot seem to get the value for a node element. Here's an example:</p>
  
  <pre><code>DECLARE @xmlWithNameSpace XML
  DECLARE @xmlWithoutNameSpace XML
  
  SET @xmlWithNameSpace = '&lt;?xml version="1.0" encoding="UTF-8"?&gt;
      &lt;Feed xmlns="gizmo"&gt;
          &lt;Product id="4444"&gt;
              &lt;ProductId&gt;4444&lt;/ProductId&gt;
          &lt;/Product&gt;
      &lt;/Feed&gt;'
  
  SET @xmlWithoutNameSpace = '&lt;?xml version="1.0" encoding="UTF-8"?&gt;
      &lt;Feed&gt;
          &lt;Product id="4444"&gt;
              &lt;ProductId&gt;4444&lt;/ProductId&gt;
          &lt;/Product&gt;
      &lt;/Feed&gt;'
  
  SELECT feed.product.value('@id[1]', 'INT') AS productId  
  FROM @xmlWithNameSpace.nodes('declare namespace ns="gizmo"; /ns:Feed/ns:Product') feed(product)
  
  UNION ALL
  
  SELECT feed.product.value('ProductId[1]', 'INT') AS productId  
  FROM @xmlWithNameSpace.nodes('declare namespace ns="gizmo"; /ns:Feed/ns:Product') feed(product)
  
  UNION ALL
  
  SELECT feed.product.value('@id[1]', 'INT') AS productId 
  FROM @xmlWithoutNameSpace.nodes('/Feed/Product') feed(product)
  
  UNION ALL
  
  SELECT feed.product.value('ProductId[1]', 'INT') AS productId 
  FROM @xmlWithoutNameSpace.nodes('/Feed/Product') feed(product)
  </code></pre>
  
  <p>This returns</p>
  
  <pre><code>4444  
  NULL  
  4444  
  4444
  </code></pre>
  
  <p>What am I doing wrong to get the value of the ProductId node (4444) when the namespace is in use?</p>
  
  <p>Thanks in advance for any guidance.</p>