title: Ignore XML namespace in T-SQL
body: <p>How do I remove/ignore the XML namespace in an xml file when querying the data with T-SQL?</p>
  
  <p>I’m loading an xml file into a variable, and it works just fine. But the xml has a namespace set, and unless I remove it, my queries come up empty.</p>
  
  <p>T-SQL:</p>
  
  <pre><code>DECLARE @xml xml
  SELECT @xml = BulkColumn FROM OPENROWSET(BULK 'C:\myfile.xml', SINGLE_BLOB) AS A
  
  SELECT X.z.value('ID[1]', 'VARCHAR(3)') FROM @xml.nodes('myroot/element') AS X(z)
  </code></pre>
  
  <p>XML sample:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;myroot xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
    &lt;element&gt;
      &lt;ID&gt;1&lt;/ID&gt;
    &lt;/element&gt;
    &lt;element&gt;
      &lt;ID&gt;2&lt;/ID&gt;
    &lt;/element&gt;
    &lt;element&gt;
      &lt;ID&gt;3&lt;/ID&gt;
    &lt;/element&gt;
  &lt;/myroot&gt;
  </code></pre>
  
  <p>This works, the query returns this:</p>
  
  <blockquote>
    <p>1<br>
    2<br>
    3  </p>
  </blockquote>
  
  <p>But the XML also contains a default namespace:</p>
  
  <pre><code>&lt;myroot xmlns="http://XXX" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
  </code></pre>
  
  <p>The <code>xmlns="http://XXX"</code> completely screws up my query. And unfortunately manually modifying the xml before loading it is not really an option.</p>
  
  <p><strong>Questions:</strong>  </p>
  
  <ul>
  <li>How do I remove or ignore the namespace when I load the data into the variable? </li>
  <li>Or how do I modify my query to handle the namespace?</li>
  </ul>