title: Extract a value from SQL XML field
body: <p>Using MS SQL Server, I've got some data in an XML field (called <em>XML</em>), which is structured like this:</p>
  
  <pre><code>&lt;Transaction01&gt;
    &lt;TransactionSetPurpose&gt;Insert&lt;/TransactionSetPurpose&gt;
    &lt;POHeader&gt;
      &lt;PO_NBR&gt;LG40016181&lt;/PO_NBR&gt;
    &lt;/POHeader&gt;
  &lt;/Transaction01&gt;
  </code></pre>
  
  <p>I'm trying to create a SQL query to fetch another column called SubmittedDate, along with the PO_NBR from this XML field.  Being new to XPath, I've read numerous examples and tried both <strong>query</strong> and <strong>value</strong>, but I've not been successful yet.  For example:</p>
  
  <pre><code>SELECT SubmittedDate, 
         XML.query('data(/POHeader/PO_NBR)') as PO_NBR
    FROM SubmitXML
  </code></pre>
  
  <p>This just gives me a empty column.  After getting a working test from Quassnoi, I worked from his XML to mine, and discovered the problem is the xmlns and xmlns:i attributes in the root node:</p>
  
  <pre><code>&lt;Transaction01 xmlns="http://services.iesltd.com/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;
  </code></pre>
  
  <p>So how do I get around that?</p>