Select values from XML with multiple namespaces

Question

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

<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.

Thom A · Accepted Answer · 2017-12-15 11:21:13Z

3

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;

edited Dec 15, 2017 at 11:21

answered Dec 15, 2017 at 9:50

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

Correct answer, +1 from my side

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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:

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 15, 2017 at 12:19

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

1 Comment

Moslem Ben Dhaou Over a year ago

Would it be possible to filter on the property @name attribute? The XML sample here only contains one for simplicity but the real one has some more

Collectives™ on Stack Overflow

Select values from XML with multiple namespaces

2 Answers 2

1 Comment

UPDATE Add a predicate

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

UPDATE Add a predicate

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related