2

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..

Patrick
  • 17,669
  • 6
  • 70
  • 85
  • 1
    There is no way that I know of to replace the entire XPath expression with a variable (except dynamic SQL). You can compare the node name to variable using `local-name()` like you do. If you don't care what the root node name is you can use `//*[local-name() = sql:variable("@xpath")]` to do a deep search for all nodes that matches `@xpath` regardless of where in the XML they are located. – Mikael Eriksson Mar 21 '12 at 15:54
  • @MikaelEriksson: Oh, okay.. I didn't think the double `//` worked, but thanks for the tip. I guess I'll use either that or just not use parameters.. – Patrick Mar 21 '12 at 17:17
  • @JeremyCaney This question was asked *more than 8 years ago*. The project I worked on at that time is no longer something I am maintaining. If you read the answers in your proposed duplicate those solutions are already mentioned in this thread and as such should not be an answer to the problem asked about here. – Patrick Sep 01 '20 at 22:20
  • @Patrick: I do apologize; I forgot about the auto-generated comment, which is downright nonsensical here. My goal was to help consolidate similar questions as, right now, there are quite a few questions asking about the same topic. I chose the other one as the "canonical" because it was more focused on the core problem, and thus most likely to help people with a similar problem in the future. – Jeremy Caney Sep 02 '20 at 18:21
  • Aha, I didn't realize the comment was due to a close vote, sorry about that. Either way, even if the questions are related this is asking how to *parameterize* the query path value as user input, and the other IIRC was simply how to use a variable. You can post a comment with the link to the other question and it will show up in the list of "linked questions" to the right though. – Patrick Sep 03 '20 at 14:10

1 Answers1

3

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Hm.. okay. So the easiest way is to use "static" xpaths and not use variables? :) – Patrick Mar 21 '12 at 13:54
  • 2
    Wait a sec... it looks like you can [reference SQL variables in XPath](http://msdn.microsoft.com/en-us/library/ms188254.aspx) – Andomar Mar 21 '12 at 13:57
  • Yeah, I found http://stackoverflow.com/questions/5965000/sql-xml-xquery-data-query-using-a-variable-in-the-node-selection which seems to reference this. Just using `sql-variable` doesn't work, but with the funny looking `*[local-name(.)=` it gets resolved.. – Patrick Mar 21 '12 at 13:59
  • Cool, yeah, I think it only allows you to pick up the value of the variable. Perhaps you can post your solution as an answer – Andomar Mar 21 '12 at 14:02
  • Yeah, I'm investigating. For the moment it doesn't work :P *I'll get back to you* – Patrick Mar 21 '12 at 14:06
  • Yeah, you need to put quotation marks arount @path, as in `sql:variable("@path")`. The trouble still is that you can't seem to add the / delimiter to the variable, so you can use either // to only search for the inner node, or use two variables, one for each "path", as in `xpath1=data`, `xpath2=node` and query it as `/xpath1/xpath2` so to speak. It would work, but not to my satisfaction.. – Patrick Mar 21 '12 at 22:21