XML Output is Truncated in SQL

Question

I need to return my result set in XML and this works fine, but if the number of records are increased, my xml output is truncated here is my query

select t.id,t.name,t.address from test FOR XML AUTO, ROOT('Response'), ELEMENTS 

However I have set some option to increase the output result set like..

Tools --> Options --> Query Results --> SQL Server --> Results to Text --> Maximum               number of characters displayed in each column Tools --> Options --> Results --> Maximum characters per column 

but still I am unable to get my desired result.

please suggest my solution

EDIT: However when I click on the XML, it open in another query window and all xml shown here, but it not return all xml instead of truncated xml.

Thanks....

Answer 1

Try the following:

Declare @xmldata xml set @xmldata = (select ... From test for xml...) select @xmldata as returnXml