Lee Linares - Friday, November 16, 2018 1:36 PM
Hi Lee,
Thanks for bringing this to my attention. I'm guilty here of using a technique without fully validating it! I first noticed the technique in Adam Machanic's sp_whoisactive. http://whoisactive.com/docs/10_commands/ provides an illustration of the approach. In older versions of SSMS, there was no support for "Retain CR/LF on copy or save", so one trick was to embed the SQL in and XML definition. Modern SSMS versions have support for CR/LF retention, but they still have a restriction on the maximum size of the text that can be retrieved, so XML is still a workaround.
There is a thread on this subject at https://sqlblogcasts.com/blogs/martinbell/archive/2009/10/25/How-to-display-long-text-in-SSMS.aspx . Unfortunately, I'm stymied when it comes to a robust solution. I can come up with solutions that escape the problematic sequences (in this case, I believe it to be '?>'), but that requires a human to unescape the escapes after copying out the body. It also falls victim to ambiguity between escaped sequences that match unescaped sequences in the original SQL. Note that whilw experimenting, I discovered the '--' in the closing '--?>' is unnecessary.
Another approach would be to bypass the XML workaround and use SSMSBoost, but that feature isn't available in the Community Edition.
Here is a quick test suite for playing around with approaches:WITH
Tests AS (
SELECT V.[SQL]
FROM ( VALUES
( N'SELECT *'+NCHAR(13)+NCHAR(10)+
N'FROM <bar> Foo' ),
( N'SELECT *, ''?>'''+NCHAR(13)+NCHAR(10)+
N'FROM <bar> Foo' ),
( N'SELECT *, ''--'''+NCHAR(13)+NCHAR(10)+
N'FROM <bar> Foo' ),
( NULL )
) AS V([SQL])
)
SELECT
[SQL],
TRY_CAST(N'<?def --' + NCHAR(13)+NCHAR(10) + [SQL] +
NCHAR(13)+NCHAR(10) + N'--?>' AS xml) AS ProcessingInstruction,
TRY_CAST(N'<?def --' + NCHAR(13)+NCHAR(10) + REPLACE([SQL], '?>', '? >') +
NCHAR(13)+NCHAR(10) + N'?>' AS xml) AS ProcessingInstructionEscape,
TRY_CAST(N'<!--' + NCHAR(13)+NCHAR(10) + [SQL] +
NCHAR(13)+NCHAR(10) + N'-->' AS xml) AS Comment
FROM Tests;