Issue using attribute variable in T-SQL XML value()

Question

DECLARE @NodePath VARCHAR(20) = 'C',
        @NodeVariable VARCHAR(20) = '@name',
        @result XML;
SET @result = '
<A>
    <B>
        <C name="Name01"/>
    </B>
    <B>
        <C name = "Name02"/>
    </B>
</A>
'
SELECT T.c.value('sql:variable("@NodeVariable")', 'VARCHAR(20)')
FROM @result.nodes('//*[local-name()=sql:variable("@NodePath")]') T(c)

I want to acquire the attributes of the XML, like:

|Name|
|Name01|
|Name02|

But the result is:

|Name|
|@name|
|@name|

How to solve that problem? Since I want to create a function that takes path and attribute name as arguments, OPENXML function is not allowed.

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

A bit simpler might be this:

SELECT x.n.value('.', 'nvarchar(20)') as 'Name'
FROM @result.nodes('/A
                    /B
                    /*[local-name() =sql:variable("@NodePath")]
                    /@*[local-name()=sql:variable("@NodeVariable")]') x(n)

The idea in short:

Dive down below <B> (or use the deep search with // if you can be sure, that there will be no <C> in any other place)
Find any element with the given name
pick the attribute with the given name (attributes are singleton per element per definition)
use value() on the current node to return the content.

What might disturb this: Multiple occurences of <C> below <B>

UPDATE Some additions to XPath and `local-name()`

Just try this:

declare @result xml =
N'<A>
    <B>
        <C name="Name01"/>
    </B>
    <TheSecondInA />
    <B>
        <C name = "Name02"/>
    </B>
    <OneMore someAttr="x" oneMoreAttr="y" theLastAttr="z" >SomeText</OneMore>
</A>';

SELECT @result.value('local-name((//TheSecondInA)[1])','varchar(100)')
      ,@result.value('local-name((/A/*[2])[1])','varchar(100)')
      ,@result.value('local-name(/A[1]/*[2])','varchar(100)')
      ,@result.value('local-name((//*[@someAttr]/@*[2])[1])','varchar(100)')
      ,@result.value('local-name((/A/OneMore/@*[3])[1])','varchar(100)')
      ,@result.value('local-name((/A/OneMore/@*[last()])[1])','varchar(100)')

      ,@result.value('local-name((/A/OneMore/text())[1])','varchar(100)')
      ,@result.value('local-name((/DoesNotExist)[1])','varchar(100)')

As you can see, the function local-name() must get a singleton XPath.

The deep search dives to the first occurance of a named node
The same is returned by the second element below <A>
We do not need this (SomeXpath)[1] if the path itself guarantees to return a singleton.
Here we dive to the first element where there is an attribute called someAttr and pick the second attribute by its position.
Similiarly we can pick the third attribute on a given path
To get the very last attribute (or element) we can use last()
If the current node is a text() node, or if the element does not exist, we get an empty string back.

Hint: With similiar XPath expressions you can use .value() to retrieve local content, .exist() to test for existance (or the lack of it) and to modify a given location...

Dale K · Accepted Answer · 2020-06-17 06:44:46Z

2

In the value() function you need to use the @*[local-name()=...] syntax e.g.:

declare @NodePath nvarchar(20) = 'C';
declare @NodeVariable nvarchar(20) = 'name';
declare @result xml =
N'<A>
    <B>
        <C name="Name01"/>
    </B>
    <B>
        <C name = "Name02"/>
    </B>
</A>'
select x.n.value('(@*[local-name()=sql:variable("@NodeVariable")])[1]', 'nvarchar(20)') as 'Name'
from @result.nodes('//*[local-name()=sql:variable("@NodePath")]') x(n)

Which yields:

Name
------
Name01
Name02

edited Jun 17, 2020 at 6:44

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jun 17, 2020 at 6:34

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

7 Comments

Dale K Over a year ago

Can you explain how that works please. I thought local-name() returned the element name? But you're using it to return the attribute name - and I really want to know how :) thanks. Also why is the "*" necessary in @*[.

AlwaysLearning Over a year ago

The local-name() function operates on node sets. When using @*[] the context of node sets are attribute names as opposed to element names.

AlwaysLearning Over a year ago

I wish the W3C XPath document was more clear on it. I kind of discovered by accident one day when reading XPathNavigator.LocalName Property and saw it talking about XPathNodeTypes of Element, Attribute, Namespace and ProcessingInstruction, and thought... hey, what if?

Gottfried Lesigang Over a year ago

@DaleK, I've added some additions to local-name() to my answer, might be interesting to you...

AlwaysLearning Over a year ago

@DaleK the @*[] is a full wildcard, as with elements, so will match all attributes of all namespaces. You can also do @foo:*[] to match only elements in the foo namespace prefix, or @*:bar[] to only match attributes named bar (regardless of their namespace prefix).

|

Collectives™ on Stack Overflow

Issue using attribute variable in T-SQL XML value()

2 Answers 2

UPDATE Some additions to XPath and `local-name()`

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

UPDATE Some additions to XPath and local-name()

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related

UPDATE Some additions to XPath and `local-name()`