SQLServer:从VARCHAR(MAX)字段替换无效的XML字符

有一个技巧使用隐式转换VARBINARY为base64和back:

在这里你的邪恶名单

DECLARE @evilChars VARCHAR(MAX)=
  CHAR(0x0)
+ CHAR(0x1)
+ CHAR(0x2)
+ CHAR(0x3)
+ CHAR(0x4)
+ CHAR(0x5)
+ CHAR(0x6)
+ CHAR(0x7)
+ CHAR(0x8)
+ CHAR(0x9)
+ CHAR(0xa)
+ CHAR(0xb)
+ CHAR(0xc)
+ CHAR(0xd)
+ CHAR(0xe)
+ CHAR(0xf)
+ CHAR(0x10)
+ CHAR(0x11)
+ CHAR(0x12)
+ CHAR(0x13)
+ CHAR(0x14)
+ CHAR(0x15)
+ CHAR(0x16)
+ CHAR(0x17)
+ CHAR(0x18)
+ CHAR(0x19)
+ CHAR(0x1a)
+ CHAR(0x1b)
+ CHAR(0x1c)
+ CHAR(0x1d)
+ CHAR(0x1e)
+ CHAR(0x1f)
+ CHAR(0x7f);

这有效

DECLARE @XmlAsString NVARCHAR(MAX)=
(
    SELECT @evilChars FOR XML PATH('test')
);
SELECT @XmlAsString;

结果(一些是"打印")

&＃x00;&＃x01;&＃x02;&＃x03;&＃x04;&＃x05;&＃x06;&＃x07;&＃x08;    
&＃x0B;&＃x0C;&＃x0D;&＃x0E;&＃x0F;&＃x10;&＃x11;&＃x12;&＃x13;&＃x14;&＃x15;&＃x16;&＃x17;&＃x18;&＃x19;&＃x1A;&＃x1B;&＃x1C;&＃x1D;&＃x1E;&＃x1F;

以下是禁止的

SELECT CAST(@XmlAsString AS XML)

但是您可以使用VARBINARY到base64的隐式转换

DECLARE @base64 NVARCHAR(MAX)=
(
    SELECT CAST(@evilChars AS VARBINARY(MAX)) FOR XML PATH('test')
);
SELECT @base64;

结果

AAECAwQFBgcICQoLDA0ODxAREhMUFRYXGBkaGxwdHh9/

现在你已经拥有了真正的XML,包括特殊字符!

SELECT CAST(CAST(@base64 AS XML).value('/test[1]','varbinary(max)') AS VARCHAR(MAX)) FOR XML PATH('reconverted')