How do I create parametrized XPath queries in SQL server?

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.

Saulius Valatka · Accepted Answer · 2009-07-16T17:30:30.307

8

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.

edited Jul 16 '09 at 17:30

answered Jul 16 '09 at 16:13

Saulius Valatka

7,017
5
26
27

Saulius, why does your solution work but not in my case? see: http://goo.gl/pI4Ap – ekkis Sep 13 '12 at 00:18

score 0 · Answer 2 · edited Dec 06 '12 at 13:35

0

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

edited Dec 06 '12 at 13:35

Mxyk

10,678
16
57
76

answered Nov 24 '10 at 05:41

krishnaroopa

1

1

I don't think that would work because you can pass in a where clause as a parameter. You could turn the whole thing into a string and concatenate the @clause to it and that would work, but then that would not really be a parameterized query that would be no different than typing in the whole string, so you lose the saftey of the parameters when trying to avoid sql injection. The way Saulius did it works well. – Chris Mullins Nov 24 '10 at 12:27

How do I create parametrized XPath queries in SQL server?

2 Answers2

Linked