SQL Server:从VARCHAR(MAX)字段替换无效的XML字符
发布时间:2021-01-17 12:55:51 所属栏目:MsSql教程 来源:网络整理
导读:我有一个VARCHAR(MAX)字段,它以XML格式连接到外部系统.接口抛出以下错误: mywebsite.com-2015-0202.xml:413005: parser error : xmlParseCharRef: invalid xmlChar value 29ne and Luke's family in Santa Fe. You know you have a standing invitation,#x1D
我有一个VARCHAR(MAX)字段,它以XML格式连接到外部系统.接口抛出以下错误: mywebsite.com-2015-0202.xml:413005: parser error : xmlParseCharRef: invalid xmlChar value 29 ne and Luke's family in Santa Fe. You know you have a standing invitation, ^ mywebsite.com-2015-0202.xml:455971: parser error : xmlParseCharRef: invalid xmlChar value 25 The apprentice nodded,because frankly,who hadnt? That diseases like chol ^ mywebsite.com.com-2015-0202.xml:456077: parser error : xmlParseCharRef: invalid xmlChar value 28 bon mot; a sentimental love of nature and animals; the proverbial British  ^ mywebsite.com-2015-0202.xml:472073: parser error : xmlParseCharRef: invalid xmlChar value 20 "Andyou want that?" ^ mywebsite.com-2015-0202.xml:492912: parser error : xmlParseCharRef: invalid xmlChar value 25 She couldnt live like this anymore. 我们发现以下字符列表无效: �         	 
   
                    我正在尝试清理这些数据,并且我找到了一个SQL函数来清理这些字符here.但是,该函数将NVARCHAR(4000)作为输入参数,因此我将函数更改为使用VARCHAR(MAX). 有人可以建议将NVARCHAR(4000)更改为VARCHAR(MAX)会产生错误的结果吗?对不起,我无法在本地测试此界面,所以想要寻求意见/建议. 原始功能: CREATE FUNCTION fnStripLowAscii (@InputString nvarchar(4000)) RETURNS nvarchar(4000) AS BEGIN IF @InputString IS NOT NULL BEGIN DECLARE @Counter int,@TestString nvarchar(40) SET @TestString = '%[' + NCHAR(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) + ']%' SELECT @Counter = PATINDEX (@TestString,@InputString COLLATE Latin1_General_BIN) WHILE @Counter <> 0 BEGIN SELECT @InputString = STUFF(@InputString,@Counter,1,NCHAR(164)) SELECT @Counter = PATINDEX (@TestString,@InputString COLLATE Latin1_General_BIN) END END RETURN(@InputString) END 修改版本: CREATE FUNCTION [dbo].RemoveInvalidXMLCharacters (@InputString VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN IF @InputString IS NOT NULL BEGIN DECLARE @Counter INT,@TestString NVARCHAR(40) SET @TestString = '%[' + NCHAR(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) + ']%' SELECT @Counter = PATINDEX (@TestString,@InputString COLLATE Latin1_General_BIN) WHILE @Counter <> 0 BEGIN SELECT @InputString = STUFF(@InputString,' ') SELECT @Counter = PATINDEX (@TestString,@InputString COLLATE Latin1_General_BIN) END END RETURN(@InputString) END 解决方法使用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; 结果(一些是“打印”) <test>� 
</test> 以下是禁止的 SELECT CAST(@XmlAsString AS XML) 但是您可以使用VARBINARY到base64的隐式转换 DECLARE @base64 NVARCHAR(MAX)= ( SELECT CAST(@evilChars AS VARBINARY(MAX)) FOR XML PATH('test') ); SELECT @base64; 结果 <test>AAECAwQFBgcICQoLDA0ODxAREhMUFRYXGBkaGxwdHh9/</test> 现在你已经拥有了真正的XML,包括特殊字符! SELECT CAST(CAST(@base64 AS XML).value('/test[1]','varbinary(max)') AS VARCHAR(MAX)) FOR XML PATH('reconverted') (编辑:西双版纳站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |