How To Select The Entire Contents Of A Varchar(max) Or NVarchar(max) Column In SQL Server Management Studio



TODO:

Have you ever wanted to select a varchar(max), or nvarchar(max) column in SSMS?  You will notice that the data is truncated when you have a large amount of data stored in the column.  To get around this we will convert the string to XML, then save the results to an XML file, so you can open in your favorite XML editor / viewer.

 

SOLUTION:

 

--to select a column with NON-XML data
select convert(xml,'<xml><![CDATA[' + cast(LogData as varchar(max)) + ']]></xml>') FROM LogTable where Id = 1234

--to select a column with XML data
select convert(xml, LogData) FROM LogTable where Id = 1234

 

 

NOTES:

The first example wraps the data in CDATA tags, so that it will be well-formed XML. 

The second example does not, because I am storing well-formed XML in the column already.