从具有多个名称空间的XML中选择值

I need to read a value of an attribute from an XML column. The data is an XML with multiple namespaces declared:

我需要从XML列中读取属性的值。数据是一个声明有多个名称空间的XML:

<sd:objectData xmlns:sd="http://sd-uri">
    <sd:object sourceKey="FC5A0A51-7FB6-4C64-A13E-D4B00649E80E">
        <do:properties xmlns:do="http://do-uri">
            <do:property name="DECISION">
                <do:propertyValues clearExistingValues="true">
                    <do:propertyValue action="add" valueInteger="1000142" tag="Approve" />
                </do:propertyValues>
            </do:property>
        </do:properties>
    </sd:object>
</sd:objectData>

I want to read the value of valueInteger, namely in this example 1000142. I tried with WITH XMLNAMESPACES() but I am not able to get it together to define both aliases.

我想读取valueInteger的值，即在这个示例1000142中。我尝试使用XMLNAMESPACES()，但我无法将其组合在一起来定义两个别名。

2 个解决方案

#1

In addition to Larnu's answer (which is the best and correct answer) just some alternative shortcuts, if you just want to get one value:

除了Larnu的答案(这是最好的和正确的答案)之外，如果你只想得到一个值，只需一些其他的快捷方式:

This query fetches the needed value in four different approaches

该查询以四种不同的方法获取所需的值

SELECT @XML.value(N'(//*/@valueInteger)[1]',N'int') AS Super_easy_with_double_wildcard
      ,@XML.value(N'(//*:propertyValue/@valueInteger)[1]',N'int') AS Easy_with_namespace_wildcard
      ,@XML.value(N'declare namespace do="http://do-uri";
                    (//do:propertyValue/@valueInteger)[1]',N'int') AS with_local_declaration
      ,@XML.value(N'declare namespace do="http://do-uri";
                    declare namespace sd="http://sd-uri";
                    (/sd:objectData/sd:object/do:properties/do:property/do:propertyValues/do:propertyValue/@valueInteger)[1]',N'int') AS with_full_local_declaration;

The general advise is: Be as specific as possible to avoid hassels. If you do no bother and you just need a readable, quick catch, you can take one of the alternatives.

一般的建议是:尽量具体，避免流苏。如果你不麻烦，你只需要一个可读的，快速捕捉，你可以选择一个替代方案。

UPDATE Add a predicate

With a predicate you can place a filter:

使用谓词，您可以放置一个过滤器:

SELECT @XML.value(N'(//*:property[@name="DECISION"]//*:propertyValue/@valueInteger)[1]',N'int') AS Example_with_predicate

#2

Does this work for you?

这对你有用吗?

DECLARE @XML xml = '
<sd:objectData xmlns:sd="http://sd-uri">
    <sd:object sourceKey="FC5A0A51-7FB6-4C64-A13E-D4B00649E80E">
        <do:properties xmlns:do="http://do-uri">
            <do:property name="DECISION">
                <do:propertyValues clearExistingValues="true">
                    <do:propertyValue action="add" valueInteger="1000142" tag="Approve" />
                </do:propertyValues>
            </do:property>
        </do:properties>
    </sd:object>
</sd:objectData>';

WITH XMLNAMESPACES ('http://sd-uri' AS sd,
                    'http://do-uri' AS do)
SELECT @XML.value('(/sd:objectData/sd:object/do:properties/do:property/do:propertyValues/do:propertyValue/@valueInteger)[1]','int') AS valueInteger;