CODEWITHSUNDEEP
sqlsql-server-2008xquerysqlxmlsqlparameter
Patrick
Patrick

Reputation: 17973

Stored procedure parameter with XML query in MSSQL gives "argument must be string literal"

I'm trying to query a table with a column which is xml data with the query and value functions. When using regular string literals it's all okay, but if I put that in a stored procedure and try to use variables it doesn't work.

I suppose I'm not using the correct datatype, but after some searching I can't figure out what datatype the query function wants.

Example: table contains

| Id | xmldata                         |
| 1  | <data><node>value</node></data> |

now, using the select query

select id
from table
where xmldata.query('/data/node').value('.', 'VARCHAR(50)') = 'value'

gets me the data I want. But, if I use this in a stored procedure and use a parameter @xpath varchar(100) and pass that to the query method as xmldata.query(@xpath) i get the error

The argument 1 of the xml data type method "query" must be a string literal.

I guess varchar(100) is not correct, but what datatype can I use that would make MSSQL happy?

Update: Okay, so. Apparently you can't pass a parameter to the query method "just like that", but one can use the sql:variable in conjunction with local-name to work a part of it out. So, for instance, this will work

declare @xpath VarChar(100)
set @xpath='node'
select objectData.query('/data/*[local-name() = sql:variable("@xpath")]')
                 .value('.', 'varchar(100)') as xmldata
from table

and value is selected in the column xmldata. But(!) it requires that the root node is the first value in the query function. The following will not work

declare @xpath VarChar(100)
set @xpath='/data/node'
select objectData.query('*[local-name() = sql:variable("@xpath")]')
                 .value('.', 'varchar(100)') as xmldata
from table

notice how the query path is "moved up" to the variable. I will continue my investigations..

Upvotes: 2

Views: 4132

Answers (1)

Andomar
Andomar

Reputation: 238086

A literal is the opposite of a variable. The message means that you cannot pass a variable as the first argument to query.

One way around that is dynamic SQL:

declare @sql varchar(max)
set @sql = 'select id from table where xmldata.query(''' + @path + 
    ''').value(''.'', ''VARCHAR(50)'') = ''value'''
exec @sql

As you can see, dynamic SQL does not result in very readable code. I would certainly investigate alternatives.

EDIT: Your suggestion of local-name() works for node names.

declare @nodename varchar(max)
set @nodename = 'node'
...
where   xmldata.query('//*[local-name()=sql:variable("@nodename")]')
            .value('.', 'varchar(50)') = 'value'

There doesn't seem to be an equivalent for paths.

Upvotes: 3

Related Questions