It would be great to create a library of XQuery strings maintained in the database that developers could use and DBAs could manage. Unfortunately, you are going to have to jump through some hoops to make that happen in SQL Server 2005. The problem originates from the fact that the XML datatype "query" method can only take a literal string value.

This also causes problems if you want to use dynamic queries. You can create a SQL statement involving a dynamic query, but what you have to do is piece together the entire query as a string that is called in the dynamic SQL stored procedure "sp_executesql". But, you cannot pass the XQuery as a variable in dynamic queries either.

Below are some examples of what works and what does not.

The first example shows a typical XQuery call, then one that will not work that uses a variable passed to the query method.

-- This works! DECLARE @myResult xml;   SELECT @myResult = Study.query(  '   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ' ) FROM JustXML WHERE ID = 1;   SELECT @myResult; GO   -- This does not (PASSING VARIABLE INSTEAD OF STRING LITERAL)   DECLARE @myResult xml; DECLARE @myXquery nvarchar(500);   SET @myXquery =  '   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ';     SELECT @myResult = Study.query(@myXquery) FROM JustXML WHERE ID = 1;   SELECT @myResult; GO

Here are two examples, one that works and one that does not, using dynamic SQL queries.

-- Using Dynamic Queries with XQuery -- --------------------------------- -- THIS WORKS (dynamic sql) DECLARE @myXquery nvarchar(255);   SET @myXquery =  N'   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ' DECLARE @myDynamicSql nvarchar(500);   SET @myDynamicSql = N'SELECT Study.query(''' + @myXquery +        ''')  AS MYRESULT FROM JustXML WHERE ID = 1'   EXECUTE sp_executesql  @myDynamicSql;   -- But this does not!!! DECLARE @myXquery nvarchar(255);   SET @myXquery =  N'   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ' DECLARE @myDynamicSql nvarchar(500); DECLARE @ParmDefinition NVARCHAR(500);     SET @myDynamicSql = N'SELECT Study.query(@thisXquery)  AS MYRESULT FROM JustXML WHERE ID = 1' SET @ParmDefinition = N'@thisXquery nvarchar(255)';     EXECUTE sp_executesql  @myDynamicSql, @ParmDefinition,                        @thisXquery = @myXquery;