FOR XML PATH(''): 转义“特殊"人物

2022-01-01 00:00:00 sql-server-2005 tsql sql-server for-xml-path

此代码基本上将基于一个字符串中的位置的字符转换为另一个字符串中相同位置的字符，并针对表中的所有行运行.

This code basically translates characters based on position in one string to the character at the same position in another string and it runs for all rows in the table.

当我运行这个(简化版)时:

When I run this (simplified version):

DECLARE @R           char(40)
DECLARE @U           char(40)
SET @R=' abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+'+char(181)
SET @U=REVERSE(@R)

DECLARE @TestTable TABLE (RowID int identity(1,1) primary key, Unreadable  varchar(500))
INSERT INTO @TestTable VALUES ('+µt$zw!*µsu+yt!+s$xy')
INSERT INTO @TestTable VALUES ('%*!!xµpxu!(')
INSERT INTO @TestTable VALUES ('pxpµnxrµu+yµs%$t')


    ;WITH CodeValues AS
    (
    SELECT
        Number,SUBSTRING(@R,Number,1) AS R,ASCII(SUBSTRING(@U,Number,1)) AS UA
        FROM Numbers
        WHERE Number<=LEN(@R)
    )
    SELECT
        t.RowID
            ,(SELECT
                  ''+c.R
                  FROM Numbers               n
                      INNER JOIN CodeValues  c ON ASCII(SUBSTRING(t.Unreadable,n.Number,1))=c.UA
                  WHERE n.Number<=LEN(t.Unreadable) 
                  FOR XML PATH('') 
             ) AS readable
        FROM @TestTable t

我得到以下信息:

RowID       readable
----------- ---------------------------------------
1           a&#x20;simple&#x20;translation
2           hello&#x20;world
3           wow&#x20;you&#x20;ran&#x20;this

但需要:

RowID       readable
----------- ---------------------------------------
1           a simple translation
2           hello world
3           wow you ran this

除了 REPLACE() 之外，还有什么方法可以让空格正确显示?在我的实际代码中，这也发生在换行符上.

Is there any way, other than REPLACE(), to have the spaces show up properly? This also happens on line breaks, in my actual code.

这可以用更好的方式重写吗?我基本上只是使用 FOR XML PATH('') 将各个行值连接在一起.

Can this be rewritten in a better way? I basically just used the FOR XML PATH('') to concatenate the individual row values together.

推荐答案

您得到的 XML 是正确的.它是 XML，而不是文本，并且可以被 XML 解析器作为 XML 读取.特殊字符被正确转义，因为它们应该是.无论您拥有使用 XML 的任何客户端模块，都应将其解析为 XML，而不是文本，然后才能正确显示.

The XML you get is correct. It is XML, not text, and readable as XML by an XML parser. Special characters are properly escaped, as they should be. Whatever client module you have that consumes that XML should parse it as XML, not as text, and then it will display properly.

更新:

如果不清楚，您在查询中需要做的就是将 XML 视为 XML 并将文本视为文本，而不是将 XML 混合为文本，即:

In case is not clear, all you need to do in your query is to treat XML as XML and text as text, not mix XML as text, ie:

;WITH CodeValues AS
    (
    SELECT
        Number,SUBSTRING(@R,Number,1) AS R,ASCII(SUBSTRING(@U,Number,1)) AS UA
        FROM Numbers
        WHERE Number<=LEN(@R)
    )
, XmlValues AS (
SELECT
        t.RowID
            ,(SELECT
                  ''+c.R
                  FROM Numbers               n
                      INNER JOIN CodeValues  c ON ASCII(SUBSTRING(t.Unreadable,n.Number,1))=c.UA
                  WHERE n.Number<=LEN(t.Unreadable) 
                  FOR XML PATH(''), TYPE
             ) AS readable
        FROM @TestTable t)
SELECT x.RowId,
    x.readable.value('.', 'VARCHAR(8000)') as readable
    FROM XmlValues AS x

相关文章