Error storing the result of select for xml into an xml variable (TSQL)

Question

I am having trouble storing the result of a "select for xml" into an xml variable. I am trying to do something like this:

declare @m xml
select @m=(select value from MyTable for xml auto)
select @m as m

If MyTable contains only few records then anything is fine but, when MyTable contains more records (Ex:4700) the result is empty. It's intresting that even for more than 10000 records the select the result is OK:

select value from MyTable for xml auto

works fine but:

select value from MyTable for xml auto, type

is empty.

I did something wrong or is it a bug? Is there any workaround for this? The xml variable is important for me because I want to make some operations later on the xml content using XML-DML. I'm using MSSQL2008 SP1

Please help me!

Answer 1

According to this link (referring to their example code):

http://msdn.microsoft.com/en-us/library/ms187339.aspx

they are using the SET command to populate the variable before SELECTing it. DO you get the same error if you do this with your data?

Answer 2

It is not a SQL server issue! It worked fine all time. The bug seems to be in the editor I used: Visual Studio 2008 SP1 T-SQL query editor. Anything is fine using SQL Server Management Studio.