带有多个名称空间的SQL Server Xml查询(SQL Server Xml query with multiple namespaces)

 我在SQL服务器中有一个表，它包含一个Xml列，我无法查询它。 我对XPath知之甚少，无法确定我的查询是错误的，还是因为看起来像是有冲突的命名空间。 这里是一个例子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。 我试过的查询是这样的： 
 
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。 
 
 我注意到Contract元素定义了a的名称空间，而Envelope也定义了a的名称空间，但我不确定这是否是个问题。 
 
 如何使用XPath查询获取上述xml示例的TransactionId？ 

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>
 
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
 
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. 
 
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. 
 
How can I retrieve TransactionId using an XPath query given the above xml example?

原文：https://stackoverflow.com/questions/22818591