How do I create parametrized XPath queries in SQL

Question

I am trying to write a parametrized query in SQL server that uses a parameter value as part of the XPath, however it does not seem to work the way I would expect it to. Here is my sample:

create table ##example (xmltest xml)

declare @LanguagePath varchar(75)

set @LanguagePath = '(/languages/language[@id="en-US"])[1]'
insert into ##example 
values ('<languages>
            <language id="en-US">c</language>
            <language id="es-ES">c</language>
        </languages>')

insert into ##example 
values ('<languages>
            <language id="en-US">b</language>
            <language id="es-ES">b</language>
        </languages>')


insert into ##example 
values ('<languages>
            <language id="en-US">a</language>
            <language id="es-ES">a</language>
        </languages>')

--This is a working statement:
--select *  from ##example 
--order by xmltest.value('(/languages/language[@id="en-US"])[1]', 'varchar')


declare @SQL nvarchar(4000)
set @SQL = '
select *  from ##example 
order by xmltest.value(@LanguagePath1, ''varchar'')
'

exec sp_executesql @SQL, N'@LanguagePath1 varchar(75)',  @LanguagePath1 = @LanguagePath;

drop table ##example 

This code results in the error: The argument 1 of the xml data type method "value" must be a string literal.

Any ideas on how I can get this to work? I would like to try to make my xpath query safe from SQL injection.

Answer 1

You should use sql:variable("@LanguagePath1") instead of just @LanguagePath1. Read more about it here. Though I'm not sure if dynamic xpath will work :) However something like xmltest.value('(/languages/language[@id=sql:variable("@languageCode")])[1] should work.

Answer 2

What if the whole where condition is to be given dynamically.

@clause = xmltest.value('(/languages/language[@id="en-US"])[1]', 'varchar')=1

select *  from ##example 
where @clause