Selecting All Data from a VARCAR(MAX) Column

If you’re using VARCHAR(MAX) columns in SQL then you may occasionally want to view the stored values in SQL Server Management Studio (SSMS) as part of the debugging process.

Because a VARCHAR(MAX) field can store up to 2GB of data SSMS truncates the data returned to 8200 characters in the results tab in order to avoid hogging too much memory.

If you want to view this data anyway then you can cast it as XML which is not truncated, you can then copy the returned XML out, remove the tags and view your data.

select
	convert(xml,'<xml><![CDATA[' + jsonObject + ']]></xml>')
from
	[live].[detail]
where
	id = @resourceId

It’s not elegant but it gets the job done!