Trouble using namespace in SQL Server XML Data Type query

Question

For background see this question:

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:

DECLARE @xmlWithNameSpace XML
DECLARE @xmlWithoutNameSpace XML

SET @xmlWithNameSpace = '<?xml version="1.0" encoding="UTF-8"?>
    <Feed xmlns="gizmo">
        <Product id="4444">
            <ProductId>4444</ProductId>
        </Product>
    </Feed>'

SET @xmlWithoutNameSpace = '<?xml version="1.0" encoding="UTF-8"?>
    <Feed>
        <Product id="4444">
            <ProductId>4444</ProductId>
        </Product>
    </Feed>'

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)

This returns

What am I doing wrong to get the value of the ProductId node (4444) when the namespace is in use?

Thanks in advance for any guidance.

Rick Putnam · Accepted Answer · 2010-08-11 18:01:38Z

4

The answer is I have to define the node element I'm trying to access with the namespece as well. All of these samples return 4444 as expected:

WITH XMLNAMESPACES ('gizmo' AS nsWithXNS)

SELECT feed.product.value('@id[1]', 'INT') AS productId 
FROM @xmlWithNameSpace.nodes('/nsWithXNS:Feed/nsWithXNS:Product') feed(product)

UNION ALL

SELECT feed.product.value('nsWithXNS:ProductId[1]', 'INT') AS productId 
FROM @xmlWithNameSpace.nodes('/nsWithXNS:Feed/nsWithXNS:Product') feed(product)

UNION ALL

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('declare namespace ns="gizmo"; ns:ProductId[1]', 'INT') AS productId 
FROM @xmlWithNameSpace.nodes('declare namespace ns="gizmo"; /ns:Feed/ns:Product') feed(product)

Thanks for reading and I hope this helps someone else.

answered Aug 11, 2010 at 18:01

Rick Putnam

5561 gold badge6 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hardryv Over a year ago

The top portion question works great, but the bottom portion (with the WITH keyword) throws a syntax error in SQL-Server-2012 -- Any ideas RP?

Rick Putnam Over a year ago

Try adding a semi-colon before the WITH. I hope that helps.

Collectives™ on Stack Overflow

Trouble using namespace in SQL Server XML Data Type query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related