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-16 17:30:30Z

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, 2009 at 17:30

answered Jul 16, 2009 at 16:13

Saulius Valatka

7,2275 gold badges29 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ekkis Over a year ago

Saulius, why does your solution work but not in my case? see: stackoverflow.com/questions/12397722/…

Mxyk · Accepted Answer · 2012-12-06 13:35:01Z

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 6, 2012 at 13:35

Mxyk

10.7k16 gold badges60 silver badges76 bronze badges

answered Nov 24, 2010 at 5:41

krishnaroopa

1

1 Comment

Chris Mullins Over a year ago

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.

Collectives™ on Stack Overflow

How do I create parametrized XPath queries in SQL server?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related