无法在SQL Server XML数据类型查询中使用命名空间

For background see this question:

有关背景,请参阅此问题:

SQL Server XML数据类型查询问题

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:

我正在尝试查询SQL Server 2005中的XML对象。当XML中没有定义名称空间时,查询工作正常。但是,当命名空间元素存在时,我似乎无法获取节点元素的值。这是一个例子:

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?

在使用命名空间时,获取ProductId节点(4444)的值我做错了什么?

Thanks in advance for any guidance.

提前感谢任何指导。

1 个解决方案

#1

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:

答案是我必须定义我尝试使用namespece访问的节点元素。所有这些样本按预期返回4444:

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.

感谢阅读,我希望这有助于其他人。

#1