SQL SERVER - View full content of a text or varchar(MAX) column

Recently I came across with a problem where I had to debug the dynamic SQL stored in large dynamic SQL varchar datatype variable and could not select in the result grid or print the whole SQL script because of SQL server limitation of displaying maximum number of text when dealing with large dynamic SQL variables.

Therefore, I started to search the web and found one tricky solution. Here I want to share that solution for such kind of problem.

DECLARE @str AS VARCHAR(MAX)
SELECT @str = REPLICATE(CAST('A' AS VARCHAR(MAX)), 65535)

SELECT [processing-instruction(X)] = @str 
FOR XML PATH(''),TYPE

This will convert the dynamic SQL to XML, instead of printing the dynamic SQL to the message tab. XML
The column name [processing-instruction(x)] sends special XML instruction allowing the text to be converted, along with any special characters. XML also keeps the formatting and can store up to 2 GB of data. It should be noted that whatever value you put in parenthesis would be incorporated in the XML tags, in this case “X”. We can strip of the XML tags from the top and bottom of the query text and go ahead with debugging.

Reference: Allow large text to be displayed in as a link