sql - how to get the full resultset from SSMS

Question

How do you get back the full content of rows in SQL Server Management Studio?

If you use "results to grid" the data is encoded, so things like line breaks are lost. If you do "result to file" or "results to text" the text is limited to 8192 characters.

NOTE: I have the solution, but I am using SO to document this. If you have a better answer feel free to post it.

Answer 1

I cast it to XML

select @variable_with_long_text
 as [processing-instruction(x)] FOR XML PATH 

The processing-instruction bit is there to stop it entitising characters such as < to <