Get SQL xml attribute value using variable

Question

I have a SQL function that takes a variable called attribute, which is the xml attribute I want to get the value from. xmlPath is the full XML string.

My xml looks like this:

<EventSpecificData>
  <Keyword>
    <Word>myWord</Word>
    <Occurences>1</Occurences>
    <Context>context</Context>
  </Keyword>
</EventSpecificData>

I want to get the value for <Word>, so I pass in /Keyword/Word and set a variable to:

set @value = @xmlPath.value('(/EventSpecificData/@attribute)[1]', 'varchar(max)')

However, I don't think @attribute is actually inserting the variables string. Is there another way to do this?

I don't think you can do that - the SQL XQuery works with literal strings quite often, and most of the time, they cannot be replaced with SQL variables.... — marc_s
– marc_s, Commented Feb 10, 2012 at 16:53

Mikael Eriksson · Accepted Answer · 2012-02-11 14:28:02Z

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.

Nick Vaccaro · Accepted Answer · 2012-02-10 19:13:04Z

0

Try concatenating the string.

set @value = @xmlPath.value('(/EventSpecificData/' + @attribute + ')[1]', 'varchar(max)')

Updated answer:

Let's try CASE'ing the operation.

SELECT @value = CASE @attribute
        WHEN 'word' THEN [word]
        WHEN 'occurrence' THEN [occurrence]
        WHEN 'context' THEN [context]
        END AS [value]
FROM
(
    SELECT x.u.value('(/EventSpecificData/Keyword/Word)[1]', 'varchar(max)') AS [word]
        , x.u.value('(/EventSpecificData/Keyword/Occurrence)[1]', 'varchar(max)') AS [word]
        , x.u.value('(/EventSpecificData/Keyword/Context)[1]', 'varchar(max)') AS [word]
    FROM @xmlPath.nodes('/EventSpecificData') x(u)
) a

edited Feb 10, 2012 at 19:13

answered Feb 10, 2012 at 16:51

Nick Vaccaro

5,5126 gold badges42 silver badges60 bronze badges

3 Comments

user1200083 Over a year ago

Thanks for the response. When I do this, I get the Message "The argument 1 of the XML data type method "value" must be a string literal." since the first argument is an XQuery.

marc_s Over a year ago

@user1200083: that error message says it all - you must have a string literal - you cannot have a variable.

user1200083 Over a year ago

Ok, so I can use and sql variable in the literal like this: select xmlPath.value('(*/*[local-name()=sql:variable("attribute1")])[1]', 'varchar(MAX)') assuming that attribute1 is 'Keyword'. However, if I set attribute1 to 'Keyword/Word' then it does not return any results. It has something to do with the forward slashes, since select xmlPath.value('(*/*[local-name()=sql:variable("attribute1")]/Word)[1]', 'varchar(MAX)') does work.

Collectives™ on Stack Overflow

Get SQL xml attribute value using variable

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related