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?

Answer 1

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.

Answer 2

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