Get SQL Xml Attribute Value Using Variable

Get SQL xml attribute value using variable

Here are a couple of solutions for you.

Sample data:

declare @xml xml
set @xml = 
'<EventSpecificData>
  <Keyword>
    <Word>myWord</Word>
    <Occurences>1</Occurences>
    <Context>context</Context>
  </Keyword>
</EventSpecificData>'

Get the first value from node named Word regardless of parents. Use // to do a deep search and use local-name() to match node name.

declare @Attribute varchar(max)

set @Attribute = 'Word'
select @xml.value('(//*[local-name() = sql:variable("@Attribute")])[1]',  'varchar(max)')

Provide parent node name and attribute in separate variables using local-name() in two levels.

declare @Node varchar(max)
declare @Attribute varchar(max)

set @Attribute = 'Word'
set @Node = 'Keyword'
select @xml.value('(/EventSpecificData
                    /*[local-name() = sql:variable("@Node")]
                    /*[local-name() = sql:variable("@Attribute")])[1]',  'varchar(max)')

Since the parameter to nodes have to be a string literal it invites to use dynamic sql to solve this. It could look something like this to make it work with your original variable content.

set @Attribute = 'Keyword/Word'
declare @SQL nvarchar(max)
set @SQL = 'select @xml.value(''(/EventSpecificData/'+@Attribute+')[1]'', ''varchar(max)'')'
exec sp_executesql @SQL, N'@xml xml', @xml

But you should be aware of that if you use this you are wide open to SQL Injection attacks. Some devious end-user might come up with a attribute string that looks like this:

set @Attribute = 'Keyword/Word)[1]'', ''varchar(max)'') select @@version --'

Executing the dynamic SQL with that will give you two result sets. The select @@version is just there to show some harmless code but it might be much worse stuff in there.

You can use quotename() to prevent the SQL injection attack. It will at least prevent the attempt made by me.

set @Attribute = 'Keyword/Word'
set @SQL = 'select @xml.value('+quotename('(/EventSpecificData/'+@Attribute+')[1]', '''')+', ''varchar(max)'')'
exec sp_executesql @SQL, N'@xml xml', @xml

Is the last version using quotename()safe? Have a look at this article by Erland Sommarskog The Curse and Blessings of Dynamic SQL.

Quote:

So with quotename() and quotestring(), do we have as good protection
against SQL injection as we have with parameterised commands? Maybe. I
don't know of any way to inject SQL that slips through quotename() or
quotestring(). Nevertheless, you are interpolating user input into the
SQL string, whereas with parameterised commands, you don't.

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

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

How to extract attribute value from XML in SQL Server 2019 (v15)?

With no expected results, perhaps this is enough to get you started.

As you define a default namespace only once you get to status, you can't use a DEFAULT namespace in XMLNAMESPACES, so I name it ns and reference that instead. This gives you the value of all the terms and their id attribute:

DECLARE @XML xml = '<Individual xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Content>
        <status xmlns:d3p1="http://www.uc.se/schemas/ucOrderReply/" xmlns="http://www.uc.se/schemas/ucOrderReply/" d3p1:result="ok" />
        <uc xmlns="http://www.uc.se/schemas/ucOrderReply/">
            <xmlReply>
                <reports xmlns:d5p1="http://www.uc.se/schemas/ucOrderReply/" d5p1:lang="eng">
                    <report d5p1:id="7605089247" d5p1:name="Test1 Test2" d5p1:styp="K39" d5p1:index="0">
                        <group d5p1:id="W080" d5p1:index="0" d5p1:key="" d5p1:name="ID particulars">
                            <term d5p1:id="W08001">9760508923</term>
                            <term d5p1:id="W08002">7605089277</term>
                            <term d5p1:id="W08003">Test1</term>
                            <term d5p1:id="W08004">Test2</term>
                        </group>
                        <group d5p1:id="W1A0" d5p1:index="0" d5p1:key="" d5p1:name="UC RPB">
                            <term d5p1:id="W1A003">000000000000000022</term>
                            <term d5p1:id="W1A081">2,2 %</term>
                            <term d5p1:id="W1A082">2,18839</term>
                        </group>
                    </report>
                </reports>
            </xmlReply>
        </uc>
    </Content>
</Individual>';
WITH XMLNAMESPACES('http://www.uc.se/schemas/ucOrderReply/' AS ns,'http://www.uc.se/schemas/ucOrderReply/' AS d5p1)
SELECT X.g.value('(@d5p1:id)','varchar(20)') AS id,
       X.g.value('(text())[1]','varchar(20)') AS term
FROM @XML.nodes('Individual/Content/ns:uc/ns:xmlReply/ns:reports/ns:report/ns:group/ns:term') X(g);

I note that the XML has been changed since the initial version I used to write this answer. This answer has not been (read "won't be") adjusted for that.

How to get value from XML attribute using Sql:Variable in xquery

Try something like

ISNULL(@Xml.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]/@*[local-name() = sql:variable("@Attribute")])[1]','varchar(max)'),'') 

SQL Server: How to get the value of a XML element specifying an attribute?

Sure there is...

You also can shorten the declaration:

declare @X xml=
'<translations>
  <value lang="en-US">example</value>
  <value lang="de-DE">Beispiel</value>
</translations>';

select @X.value('(/translations/value[@lang="en-US"])[1]','varchar(max)');

The point is, that you need a singleton result when you are using .value(). You achieve this by putting the XPath in paranthesis and force the first element to be taken (in this case it's the only element).

Btw: If you need this (and sooner or later you will need this...), you might put the "en-US" as parameter into your query like this:

declare @prm VARCHAR(10)='en_US';
select @X.value('(/translations/value[@lang=sql:variable("@prm")])[1]','varchar(max)');

You can reach the similar, but with a value of the actual query by using sql:column().

Use xQuery to extract attribute values that also exist in SQL variable

You can use contains function.
Declare a variable like so:

DECLARE @ATTRIBUTES VarChar(Max) = '|AAA|BBB|CAR|XYZ|';

And in you query instead of @name = "AAA" use:

contains(sql:variable("@ATTRIBUTES"), concat("|", @name, "|"))

---

Related Topics