更新:我将之前的解决方案保留在下面,但根据 Jeremy 的帖子,我想出了一个更好的解决方案。
新方案:
DECLARE @xml XML = 'abc & xyz ><';
SELECT newstring = ((SELECT @xml FOR XML PATH(''), TYPE).value('.', 'varchar(8000)'));
返回值:
abc & xyz ><
旧方案(仍可行):
我有几个用于此类事情的函数。首先,您需要rangeAB和CharMapAB。
RangeAB
CREATE FUNCTION dbo.rangeAB
(
@low bigint,
@high bigint,
@gap bigint,
@row1 bit
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH L1(N) AS
(
SELECT 1
FROM (VALUES
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0)) T(N)
),
L2(N) AS (SELECT 1 FROM L1 a CROSS JOIN L1 b CROSS JOIN L1 c),
iTally AS (SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM L2 a CROSS JOIN L2 b)
SELECT
r.RN,
r.OP,
r.N1,
r.N2
FROM
(
SELECT
RN = 0,
OP = (@high-@low)/@gap,
N1 = @low,
N2 = @gap+@low
WHERE @row1 = 0
UNION ALL
SELECT TOP (ABS((COALESCE(@high,0)-COALESCE(@low,0))/COALESCE(@gap,0)+COALESCE(@row1,1)))
RN = i.rn,
OP = (@high-@low)/@gap+(2*@row1)-i.rn,
N1 = (i.rn-@row1)*@gap+@low,
N2 = (i.rn-(@row1-1))*@gap+@low
FROM iTally AS i
ORDER BY rn
) AS r
WHERE @high&@low&@gap&@row1 IS NOT NULL AND @high >= @low AND @gap > 0;
CharMapAB
CREATE FUNCTION dbo.charmapAB
(
@asciiOnly BIT,
@xmlCheck BIT
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH rowz(N) AS (SELECT CASE @asciiOnly WHEN 0 THEN 255 ELSE 65535 END)
SELECT
char_nbr = i.RN,
ascii_val = CHAR(cs.RN),
unicode_val = u.unicode_val,
quoted_val = uq.quoted_val,
is_unicode_only = SIGN(i.RN&256),
is_acsii_ws = CASE WHEN cs.RN IN ((2),(9),(10),(13),(32),(160)) THEN 1 ELSE 0 END,
is_ascii_blank = CASE WHEN cs.RN BETWEEN 28 AND 31
OR cs.RN BETWEEN 129 AND 159 THEN 1 ELSE 0 END,
unicode_xml_val = x.unicode_xml_val,
bin = CAST(NCHAR(cs.RN) AS varbinary)
FROM rowz
CROSS APPLY dbo.rangeAB(1,rowz.N,1,1) AS i
CROSS APPLY (VALUES(CHECKSUM(i.RN))) AS cs(RN)
CROSS APPLY (SELECT TOP (@xmlCheck*1) NCHAR(cs.RN)
WHERE @xmlCheck = 1
FOR XML PATH('')) AS x(unicode_xml_val)
CROSS APPLY (VALUES(NCHAR(cs.RN))) AS u(unicode_val)
CROSS APPLY (VALUES('"'+u.unicode_val+'"')) AS uq(quoted_val);
CharmapAB将帮助您识别哪些字符是XML:
如果您运行此查询,您可以确定哪些ASCII字符是“XML受保护的”。
SELECT cm.*
FROM dbo.CharmapAB(0,1) AS cm;
返回值(为了简洁起见而截断)
char_nbr ascii_val unicode_val quoted_val is_unicode_only is_acsii_ws is_ascii_blank unicode_xml_val bin
--------- --------- ----------- ---------- -------------------- ----------- -------------- -------------------- ------
1 "" 0 0 0  0x0100
2 "" 0 1 0  0x0200
....
32 " " 0 1 0   0x2000
33 ! ! "!" 0 0 0 ! 0x2100
34 " " """ 0 0 0 " 0x2200
35 # # "#" 0 0 0 # 0x2300
36 $ $ "$" 0 0 0 $ 0x2400
37 % % "%" 0 0 0 % 0x2500
38 & & "&" 0 0 0 & 0x2600
39 ' ' "'" 0 0 0 ' 0x2700
...
我的经验是,除了 char(9),char(10) 和 char(13)(制表符、回车和换行符)外,前31个字符很少使用。此外,char(32),char(38),char(60) 和 char(62) 也很少用到:空格、&、小于号和大于号("<" 和 ">")。以下查询可能足以获取您所需的字符:
DECLARE @yourstring VARCHAR(8000) = 'ABC&123<xxx>'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@yourstring,
'	', CHAR(9)),
'
', CHAR(10)),
'
', CHAR(13)),
' ', CHAR(32)),
'&', CHAR(38)),
'<', CHAR(60)),
'>', CHAR(62));
返回:
ABC&123
您可以使用CharMapAB根据需要进行更新。