如何在TSQL中解码JSON里含重音字符的BASE64文本

问题场景 #

有存储聊天消息的表，消息的text和attachments字段组合为对象序列化后以JSON文本存储。每个附件的oid字段是BASE64编码的文件路径，原始路径包含如Privé这类带重音的字符。使用TSQL的OPENJSON解析JSON后，通过XML技巧解码BASE64路径时，重音字符显示异常（Privé变为Privé）。

示例问题代码 #

DECLARE     @messagesTable TABLE([Id] UniqueIdentifier, [Content] NVARCHAR(MAX))

INSERT INTO @messagesTable VALUES(
   'EF69067D-428B-8DA4-4FD8-0004CF6D28C2',
   N'{"text":"<p>Docs</p>","attachments":[{"name":"doc1.pdf","oid":"UHJpdsOpXGRvYzEucGRm"},{"name":"doc2.pdf","oid":"QWxnZW1lZW5cZG9jMi5wZGY="}]}'
)

SELECT      [M].[Id], [J].[name], [J].[oid],
            CONVERT(VARCHAR(MAX), CAST('' AS XML).value('xs:base64Binary(sql:column("[J].[oid]"))', 'VARBINARY(MAX)')) AS decodedOid
FROM        @messagesTable AS [M]
CROSS APPLY OPENJSON([M].[Content], '$.attachments') WITH ([name] NVARCHAR(MAX), [oid] NVARCHAR(MAX)) AS [J]

问题原因 #

BASE64解码后的二进制数据是UTF-8编码的，但直接用CONVERT(VARCHAR(MAX), ...)会使用数据库默认的非UTF-8编码页（如1252）进行转换，导致UTF-8的重音字符被错误解析，出现乱码。

解决方法 #

修改转换逻辑，使用CONVERT(NVARCHAR(MAX), 二进制数据, 65001)，其中65001是UTF-8对应的代码页，确保将UTF-8编码的二进制数据正确转换为Unicode字符串。

修改后的正确代码 #

DECLARE     @messagesTable TABLE([Id] UniqueIdentifier, [Content] NVARCHAR(MAX))

INSERT INTO @messagesTable VALUES(
   'EF69067D-428B-8DA4-4FD8-0004CF6D28C2',
   N'{"text":"<p>Docs</p>","attachments":[{"name":"doc1.pdf","oid":"UHJpdsOpXGRvYzEucGRm"},{"name":"doc2.pdf","oid":"QWxnZW1lZW5cZG9jMi5wZGY="}]}'
)

SELECT      [M].[Id], [J].[name], [J].[oid],
            CONVERT(NVARCHAR(MAX), CAST('' AS XML).value('xs:base64Binary(sql:column("[J].[oid]"))', 'VARBINARY(MAX)'), 65001) AS decodedOid
FROM        @messagesTable AS [M]
CROSS APPLY OPENJSON([M].[Content], '$.attachments') WITH ([name] NVARCHAR(MAX), [oid] NVARCHAR(MAX)) AS [J]

执行后，第一个附件的decodedOid将正确显示为Privé\doc1.pdf。

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