Variable in Exist()

"Morten Snedker" wrote in message
news:Oug7cw3rIHA.3420@TK2MSFTNGP04.phx.gbl...
> I'm trying to get this to work:
>
> ----
> declare @s nvarchar(150)
> select @s = '(/Settings[ProductGroupID="M1_1_G1_S2"])'
> print @s
>
> select top 1 *
> FROM dbo.Paragraph
> WHERE (CAST(CustomModuleSettings as xml).exist(@s)=1)
> ----
>
> It returns:
>
> Msg 8172, Level 16, State 1, Line 17
> The argument 1 of the xml data type method "exist" must be a string
> literal.
>
> Changing to
>
> ..WHERE (CAST(CustomModuleSettings as
> xml).exist('(/Settings[ProductGroupID="M1_1_G1_S2"])')=1)
>
> , and all is well.
>
> What am I doing wrong?
>
>
> Regards /Snedker

As far as I know nothing, it's a limitation of SQL Server's XML support. The
workaround for similar situations is to construct the SQL string dynamically
and then use exec or sp_ExecuteSql.

--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name