Dynamic XQuery Expressions
We have had several requests through our message boards for details on how to execute dynamic XQuery expressions. A dynamic XQuery expression is one whose exact details are not know at the time when the query is written. For example, consider the following XML fragment stored in a single XML data-type instance:
<foo id="1">foo 1</foo>
<foo id="2">foo 2</foo>
<foo id="3">foo 3</foo>
Suppose we have some client side logic where the user is able to choose which of the 3 foo elements that want to return. The XQuery expression used in this case is going to depend on the particular item requested and will not be known until the user makes their decision (i.e. it can not simply be hard-coded as a string). Since the XML data-type methods require a string constant as their input, there is no way to directly specify dynamic XQuery expressions. There are some workarounds for this problem, depending on exactly what part of the query is dynamic.
The easiest option is where the query simply needs to be parametrized. As is the case with the example above, the dynamic part of the query simply consists of a constant value that is different for each query execution. The easiest way to parametrize a query is to use the sql:variable() XQuery function and store the parameter value in the associated T-SQL variable. For example:
declare @index int
set @index=2
select data.query( N'
/foo[position()=sql:variable("@index")]
') from test
In this case, the index for the required item is stored in the T-SQL variable @index and this is then referenced in the XQuery expression through the sql:variable() function. Notice that we can not simply use the value of the sql:variable() function as an ordinal predicate - rather, we need to explicitly compare it's value to that of the position() function. This is due to a limitation of the XQuery implementation inside SQL Server 2005 that requires ordinal predicates to be numeric literal values (i.e. they can not be something that changes at query execution time).
While the parametrization of queries is a powerful tool, it does not solve the general problem of being able to execute truly dynamic XQuery expressions. In order to do this, you need to use the T-SQL exec function. The T-SQL exec function allows any string value to be executed as a T-SQL expression. For example:
declare @query nvarchar(200);
set @query=N'
select data.query( N''
/foo[1]
'') from test1'
exec( @query )
The part of the query marked in red is the dynamic XQuery expression. Notice that the entire T-SQL expression (i.e. including the select ... from part of the statement) needs to be included in the dynamic query string. Also, notice that the ' characters that are used to contain the XQuery part of the expression need to be escaped (as '') in order to make the query string valid.
While this is not an ideal solution to the problem of dynamic queries, most cases can be handled by the parametrization option and the exec function should only be used as a last resort.