Skip to content

Instantly share code, notes, and snippets.

@chadbaldwin

chadbaldwin/SSMS Clicky Column.sql

Last active September 18, 2025 20:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save chadbaldwin/2420a639f89a379e270ab79a602653b8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save chadbaldwin/2420a639f89a379e270ab79a602653b8 to your computer and use it in GitHub Desktop.
Download ZIP
Create an XML clickable column in SSMS
Raw
SSMS Clicky Column.sql
DROP TABLE IF EXISTS #clicky_data;
CREATE TABLE #clicky_data (
random_text nvarchar(MAX) NULL,
);
INSERT #clicky_data (random_text)
VALUES (OBJECT_DEFINITION(-495130372)) -- sys.dm_db_partition_stats
, (OBJECT_DEFINITION(-411581074)) -- sys.dm_exec_query_stats
-- , (N'This is a ?> test <? bleh')
, (N'This '+NCHAR(0)+' is a '+NCHAR(18)+N' test '+NCHAR(20)+N' bleh')
, (N'Fun with β–ˆ emojis πŸ˜„ smile')
, (NULL);
GO
------------------------------------------------------------
-- My version
------------------------------------------------------------
/*
Essentially a rip-off of the sp_whoisactive method.
The main thing I changed is the use of TRANSLATE()+REPLICATE() instead of using REPLACE(REPLACE(REPLACE(...
I also added a replacement to handle '?>'
*/
/*
Notes:
* NUL (0x00 / NCHAR(0) needs to go at the end of the bad string array otherwise it causes isues with TRANSLATE()
* Conversion to Latin1_General_Bin2 collation needs to happen before TRANSLATE otherwise NUL won't get replaced
*/
DECLARE @xml_bad nvarchar(256) = CONCAT('',0x01020304050607080B0C0E0F101112131415161718191A1B1C1D1E1F00), -- NCHAR's 0-31 (excluding CR[0x0D], LF[0x0A], TAB[0x09])
@crlf nchar(2) = NCHAR(13)+NCHAR(10); -- 0x0D000A00 works too, or 0x0D0A for varchar
DECLARE @xml_replace nvarchar(256) = REPLICATE(N'?',LEN(@xml_bad));
SELECT clicky_column = (
SELECT [processing-instruction(q)] = TRANSLATE(
REPLACE(
'--'+@crlf+c.random_text+@crlf+'--',
'?>',
'??'
) COLLATE Latin1_General_Bin2,
@xml_bad,
@xml_replace
)
FOR XML PATH(''), TYPE
)
FROM #clicky_data c;
-- As a one liner
SELECT clicky_column = (SELECT [processing-instruction(q)] = TRANSLATE(REPLACE('--'+@crlf+c.random_text+@crlf+'--','?>','??') COLLATE Latin1_General_Bin2, @xml_bad, @xml_replace) FOR XML PATH(''), TYPE)
FROM #clicky_data c;
-- As an inline scalar function
GO
CREATE OR ALTER FUNCTION dbo.udf_GetTextAsXml
(
@text nvarchar(MAX)
)
RETURNS xml
WITH INLINE = ON
AS
BEGIN
RETURN (
SELECT [processing-instruction(q)] = TRANSLATE(
REPLACE('--'
+ CHAR(13)+CHAR(10)
+ @text
+ CHAR(13)+CHAR(10)
+ '--'
, '?>', '??'
) COLLATE Latin1_General_BIN2
, CONCAT('',0x01020304050607080B0C0E0F101112131415161718191A1B1C1D1E1F00)
, '?????????????????????????????'
)
FOR XML PATH(''), TYPE
)
END;
GO
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
-- Playing around with different ways to generate the character exclusion list
DROP TABLE IF EXISTS #xml_bad;
CREATE TABLE #xml_bad (
id int NOT NULL,
charlist nvarchar(256) NOT NULL,
);
INSERT #xml_bad
VALUES (0, NCHAR(1)+NCHAR(2)+NCHAR(3)+NCHAR(4)+NCHAR(5)+NCHAR(6)+NCHAR(7)+NCHAR(8)+NCHAR(11)+NCHAR(12)+NCHAR(14)+NCHAR(15)+NCHAR(16)+NCHAR(17)+NCHAR(18)+NCHAR(19)+NCHAR(20)+NCHAR(21)+NCHAR(22)+NCHAR(23)+NCHAR(24)+NCHAR(25)+NCHAR(26)+NCHAR(27)+NCHAR(28)+NCHAR(29)+NCHAR(30)+NCHAR(31)+NCHAR(0))
, (1, CHAR(1)+CHAR(2)+CHAR(3)+CHAR(4)+CHAR(5)+CHAR(6)+CHAR(7)+CHAR(8)+CHAR(11)+CHAR(12)+CHAR(14)+CHAR(15)+CHAR(16)+CHAR(17)+CHAR(18)+CHAR(19)+CHAR(20)+CHAR(21)+CHAR(22)+CHAR(23)+CHAR(24)+CHAR(25)+CHAR(26)+CHAR(27)+CHAR(28)+CHAR(29)+CHAR(30)+CHAR(31)+CHAR(0))
, (2, (SELECT STRING_AGG(NCHAR([value]),N'') FROM GENERATE_SERIES(1,31) WHERE [value] NOT IN (13,10,9))+NCHAR(0))
, (3, 0x010002000300040005000600070008000B000C000E000F0010001100120013001400150016001700180019001A001B001C001D001E001F000000)
, (4, CONVERT(varchar(512),0x01020304050607080B0C0E0F101112131415161718191A1B1C1D1E1F00))
, (5, CONCAT('',0x01020304050607080B0C0E0F101112131415161718191A1B1C1D1E1F00))
-- I'd never use these, but it's fun that they work, maybe code golf? πŸ˜„
, (6, CONCAT('',CONVERT(varbinary(512), N'ΘΠƒΨ…ΰ ‡ΰ°‹ΰΌŽα„αŒ’α””αœ–α€˜α¬šα΄œαΌž'))+CHAR(0))
, (7, N' '+NCHAR(0));
DECLARE @sample_string nvarchar(256) = N'This ?> '+NCHAR(0)+' is a '+NCHAR(18)+N' test '+NCHAR(20)+N' <? bleh πŸ˜„',
@crlf nchar(2) = NCHAR(13)+NCHAR(10);
SELECT x.id, x.charlist, DATALENGTH(x.charlist), LEN(x.charlist), HASHBYTES('md5', x.charlist), CONVERT(varbinary(512), x.charlist)
, clicky_column = (SELECT [processing-instruction(q)] = TRANSLATE(REPLACE('--'+@crlf+@sample_string+@crlf+'--','?>','??') COLLATE Latin1_General_Bin2, x.charlist, REPLICATE(N'?',LEN(x.charlist))) FOR XML PATH(''), TYPE)
FROM #xml_bad x;
------------------------------------------------------------
------------------------------------------------------------
-- Sandbox
------------------------------------------------------------
-- Playing around with other methods...
-- sp_whoisactive method
-- https://github.com/amachanic/sp_whoisactive/blob/master/sp_WhoIsActive.sql#L4039-L4079
/*
Pros:
* Handles control characters
Cons:
* Fails to handle `?>` - Simple fix to add replace
* Very verbose syntax
*/
SELECT clicky_column = (
SELECT
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
N'--' + NCHAR(13)+NCHAR(10) +
c.random_text +
NCHAR(13)+NCHAR(10) + N'--' COLLATE Latin1_General_Bin2,
NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
NCHAR(0),
N''
) AS [processing-instruction(query)]
FOR XML
PATH(''),
TYPE
)
FROM #clicky_data c;
GO
------------------------------------------------------------
-- CDATA
/*
Pros:
* No escaping needed, doesn't encode anything (AFAIK)
* Handles `?>` without issues
* Simple / short syntax
Cons:
* Can only be used directly, cannot be encapsulated into a sub-query otherwise the clickability is lost
* If `TYPE` is added, clickability is restored, but the CDATA text is interpolated and encoded
* Fails to handle control characters
* Concatenates all rows into a single XML object
*/
SELECT Tag = 1, Parent = 0, [q!1!!CDATA] = c.random_text FROM #clicky_data c FOR XML EXPLICIT;
GO
------------------------------------------------------------
-- Special column name
/*
Pros:
* Simple / short syntax
Cons:
* Fails to handle `?>` - Simple fix to add replace
* Fails to handle control characters
* Concatenates all rows into a single XML object
* Forced column name, must use [XML_F52E2B61-18A1-11d1-B105-00805F49916B]
*/
SELECT [XML_F52E2B61-18A1-11d1-B105-00805F49916B] = c.random_text FROM #clicky_data c;
GO
------------------------------------------------------------
-- Special column name
/*
Pros:
* Simple / short syntax
Cons:
* Fails to handle `?>` - Simple fix to add replace
* Fails to handle control characters
* Concatenates all rows into a single XML object
* Forced column name, must use [Microsoft SQL Server 2005 XML Showplan]
*/
SELECT [Microsoft SQL Server 2005 XML Showplan] = c.random_text FROM #clicky_data c;
GO
------------------------------------------------------------
------------------------------------------------------------
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment