Ignore XML namespace in T-SQL

How do I remove/ignore the XML namespace in an xml file when querying the data with T-SQL?

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.

T-SQL:

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)

XML sample:

<?xml version="1.0" encoding="utf-8"?>
<myroot xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <element>
    <ID>1</ID>
  </element>
  <element>
    <ID>2</ID>
  </element>
  <element>
    <ID>3</ID>
  </element>
</myroot>

This works, the query returns this:

1

2

3

But the XML also contains a default namespace:

<myroot xmlns="http://XXX" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

The xmlns="http://XXX" completely screws up my query. And unfortunately manually modifying the xml before loading it is not really an option.

Questions:

• How do I remove or ignore the n开发者_如何学运维amespace when I load the data into the variable?
• Or how do I modify my query to handle the namespace?

Just use this:

;WITH XMLNAMESPACES(DEFAULT 'http://XXX')
SELECT 
    X.z.value('ID[1]', 'VARCHAR(3)') 
FROM 
    @xml.nodes('/myroot/element') AS X(z)

The WITH XMLNAMESPACES allows you to define namespace aliasses for your queries, and if you don't care about a specific XML namespace prefix, you can just define it as DEFAULT namespace and be done with it.

I was facing the same problem in my XML Query. Namespace "xmlns="urn:tradefeed-xsd" was creating problem and my query returns empty.

<?xml version="1.0" encoding="UTF-8" ?> 
<BatchFeed xmlns="urn:tradefeed-xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

Once I used ;WITH XMLNAMESPACES(DEFAULT 'urn:tradefeed-xsd') statement before my select statement it returns data.