How to create parameterized XPath queries on SQL server?

Question

I am trying to write a parameterized query in a SQL server that uses the parameter value as part of XPath, however, it does not seem to work as I expected. Here is my example:

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 leads to an error: Argument 1 of the method "value" of the xml data type must be a string literal.

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

Answer 1

@LanguagePath1 sql:variable("@LanguagePath1"). . , xpath:) - xmltest.value('(/languages/language[@id=sql:variable("@languageCode")])[1] .

Answer 2

, .

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

select *  from ##example 
where @clause