Search code examples
sql-serverfor-xmlquery-hints

Using Query Hint on FOR XML

does anyone know how about using Query Hints with the "FOR XML" statement? My (Sub-)Query looks like this:

SELECT ','+RTRIM(username) FROM @TmpDistinctUsers 
WHERE vergabe_nr = b1.vergabe_nr            
ORDER BY  rn DESC
FOR XML path(''),elements

When I add

OPTION (RECOMPILE)

I get Syntax errors...

thx

Solution

  • You can not add that as a part of the subquery.

    Have a look at the documentation for SELECT

    [ OPTION ( <query_hint> [ ,...n ] ) ]

    OPTION goes to the end of the main query.