The argument 1 of the XML data type method "value" must be a string literal

Question

I've read through SO: XML data type method “value” must be a string literal but my problem's a bit different. I have a bit of xml in a variable I want to pick apart and am given a path. originally I tried this:

declare @x xml
select @x = '....'
select @x.value('(' + @path + ')[1]', 'varchar(max)')

but, of course, that fails. then I found the sql:variable and tried this:

select @x.value('(sql:variable("@path"))[1]', 'varchar(max)')

but that curiously returns the value of @path (why?). I've been messing with it but can't get it to do the right thing.

Thoughts anyone?

Answer 1

Your select returns the value of @path because sql:variable() returns a literal value, so in effect you're asking SQL server to select the literal value @path from the document, which it does. The only way I know of doing what you want would be using dynamic SQL, like so:

declare @xml xml = '
<root>
    <element attr="test">blah</element>
</root>';

declare @p nvarchar(max) = '(//element/text())[1]';
declare @sql nvarchar(max) 
    = 'select @x.value(''' + @p + ''', ''nvarchar(max)'')';

exec sp_executesql @sql, @parameters = N'@x xml', @x = @xml;

But I should warn you that this is not very good practice (think about SQL injections, validating input, etc.)