Retrieving XML Data using FOR XML AUTO into sql variable

hello guru's

I am using SQL server 2000, Version 8.0(SP4)
I need to put XML string returned from SELECT FOR XML AUTO query to a variable, But i can not able to do it in Transact SQL . can you please help me out ?

Declare @.Message varchar(200)

SELECT col1,col2 from table1 FOR XML AUTO

I need to put result of above query to @.Message variable.

I have tried with following but failed ......

SET @.Message = SELECT col1,col2 from table1 FOR XML AUTO

SET @.Message = sp_executesql N'SELECT col1,col2 from table1 FOR XML AUTO'

can anyone suggest me the solution to this .....

Thanks

Which version of SQL Server are you using -- 2000 or 2005?

|||

Parentheses should help e.g.

Code Snippet

SET @.Message = (SELECT col1, col2 FROM table1 FOR XML AUTO);

|||Thanks Kent for pointing me out,

I am using SQL server 2000 Version 8.0(SP4)

I have updated the post.

Thanks

|||Hello Martin,

Parenthesis does not helped ...It is giving syntax error.

Thanks for reply
|||

Sorry, works with SQL Server 2005 I tested with but then obviously not with SQL Server 2000 which you are using.

|||

Hi,

The code you are trying to execute will not work as FOR XML is not supported to work with assignment in SQL Server 2000. This works only with 2005