SQL Server 2016下不使用REPLACE如何避免FOR XML PATH的字符编码问题

解决方案 #

你可以通过嵌套使用FOR XML PATH(...),TYPE的方式避免HTML内容被自动编码，同时完整保留你需要的表格结构，不需要调用REPLACE处理转义字符，完全兼容SQL Server 2016版本。

修正后完整代码 #

CREATE TABLE #History (UserID int, UserName Varchar(20), CountryName Varchar(2))
Insert into #History
Values(1,'Ann', 'GB'),
       (2,'Jason', 'DE'),
       (3,'Mary', 'PT')

DECLARE @Values NVARCHAR(MAX)
SELECT @Values = CAST((
    SELECT 
        -- 内层生成a标签，用TYPE避免转义
        (SELECT 
            '<a href="https://backend.com/User/UserDetails.aspx?UserID=' + CAST(UserId AS varchar(150)) + '">' + CAST(UserID AS varchar(150)) + '</a>'
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') AS 'td',
        '',
        UserName AS 'td',
        '',
        CountryName AS 'td',
        ''
    FROM #History
    -- 外层也加TYPE避免二次转义，保留表格结构
    FOR XML PATH('tr'), ELEMENTS, TYPE
) AS NVARCHAR(MAX))

SELECT @Values

DROP TABLE #History

实现逻辑说明 #

• 内层生成<a>标签的部分使用FOR XML PATH(''), TYPE生成原生XML类型内容，不会对HTML特殊字符做自动转义
• 调用.value('.', 'NVARCHAR(MAX)')把XML内容转为普通字符串时，会保留原始的HTML标签格式
• 外层FOR XML PATH('tr')也加上TYPE参数，避免二次转义，同时完整保留<tr>、<td>的表格层级结构

内容的提问来源于stack exchange，提问作者Zorge17