具有多个命名空间的 SQL Server Xml 查询 [英] SQL Server Xml query with multiple namespaces

问题描述

我在 SQL Server 中有一个包含 Xml 列的表，但查询时遇到问题.我对 XPath 的了解不够，无法确定我的查询是否错误，或者是因为命名空间冲突.这是一个示例 xml:

I have a table in SQL server that contains an Xml column and I am having trouble querying it. I don't know enough about XPath to determine if my query is wrong, or if it is because of what seems like conflicting namespaces. Here is an example xml:

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope"
            xmlns:a="http://www.w3.org/2005/08/addressing" 
            xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <s:Header>
        <!-- snip -->
    </s:Header>
    <s:Body>
        <FetchRequest xmlns="http://www.foobar.org/my/schema">
            <Contract xmlns:a="http://www.foobar.org/2014/04/datacontracts"
                      xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <a:RequestedBy>John Doe</a:RequestedBy>
                <a:TransactionId>ABC20140402000201</a:TransactionId>
            </Contract>
        </FetchRequest>
    </s:Body>
</s:Envelope>

我想从 xml 中检索 TransactionId.我试过的查询是这样的:

I want to retrieve TransactionId from the xml. The query I tried was this:

SELECT TOP 100
MessageXml,
MessageXml.value('
    declare namespace s="http://www.w3.org/2003/05/soap-envelope";
    declare namespace a="http://www.w3.org/2005/08/addressing";
    (/s:Envelope/s:Body/FetchRequest/Contract/a:TransactionId)[1]', 'varchar(max)')
FROM dbo.Message

我的 MessageXml.value 返回 NULL.如果我删除 s:Body 之后的所有内容，我似乎会得到一堆串联的文本，但是一旦我添加 FetchRequest，我的结果中就会返回 NULL.

I am getting back NULL for my MessageXml.value. If I remove everything after s:Body I seem to get a bunch of text that is concatenated, but as soon as I add FetchRequest I get NULL back in my results.

我确实注意到 Contract 元素定义了 a 的命名空间，Envelope 也定义了 a 的命名空间，但我不确定这是否有问题.

I did notice that the Contract element defines a namespace of a, and the Envelope also defines a namespace of a, but I wasn't sure if that is a problem or not.

在上述 xml 示例中，如何使用 XPath 查询检索 TransactionId?

How can I retrieve TransactionId using an XPath query given the above xml example?

推荐答案

你有两个问题:

• 您没有遵守 节点上的隐式默认 XML 命名空间
• 带有 a: 前缀的 XML 命名空间首先在 <s:Envelope> 节点上定义，然后在 < 上重新声明;Contract> 节点(在我看来真的很糟糕)，您需要对 节点下的任何内容使用 second 声明.

• you're not respecting the implicit default XML namespace on the <FetchRequest> node
• the XML namespace with the a: prefix is first defined on the <s:Envelope> node, and is being re-declared on the <Contract> node (really really bad practice in my opinion) and you need to use the second declaration for anything below the <Contract> node.

所以你需要这样的东西(我更喜欢在 WITH XMLNAMESPACES() 语句中预先定义 XML 命名空间):

So you need something like this (I prefer to define the XML namespaces upfront, in a WITH XMLNAMESPACES() statement):

;WITH XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' AS s,
                    'http://www.foobar.org/2014/04/datacontracts' AS a,
                    'http://www.foobar.org/my/schema' AS fb)
SELECT 
    MessageXml,
    MessageXml.value('(/s:Envelope/s:Body/fb:FetchRequest/fb:Contract/a:TransactionId)[1]', 'varchar(max)')
FROM 
    dbo.Message

这将输出整个查询和第二列的值 ABC20140402000201.

This will output the whole query and the value ABC20140402000201 for your second column.

这篇关于具有多个命名空间的 SQL Server Xml 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！