sql - 如何从 NVARCHAR(MAX) 属性解析编码为 UTF-8 的 XML？

Question

我在解析存储在 NVARCHAR(MAX) 类型字段中的 XML 字符串时遇到问题(我无法更改此字段的类型)。

这是我的表(工作时间):

CREATE TABLE WorkingHours(
    [ID] [int] NOT NULL PRIMARY KEY,
    [CONTENT] [nvarchar](MAX) NOT NULL,
    -- ...
);

这是 [CONTENT] 属性的示例:

<?xml version="1.0" encoding="UTF-8"?>
    <calendar>
        <day number="1" worked_day="no">
            <interval number="1" begin_hour="08:30" end_hour="12:00"/>
            <interval number="2" begin_hour="13:30" end_hour="17:00"/>
            <interval number="3" begin_hour="" end_hour=""/></day>
        <day number="2" worked_day="no">
            <interval number="1" begin_hour="08:30" end_hour="12:00"/>
            <interval number="2" begin_hour="13:30" end_hour="17:00"/>
            <interval number="3" begin_hour="" end_hour=""/>
        </day>
        <day number="3" worked_day="no">
            <interval number="1" begin_hour="08:30" end_hour="12:00"/>
            <interval number="2" begin_hour="13:30" end_hour="17:00"/>
            <interval number="3" begin_hour="" end_hour=""/>
        </day>
        <day number="4" worked_day="no">
            <interval number="1" begin_hour="08:30" end_hour="12:00"/>
            <interval number="2" begin_hour="13:30" end_hour="17:00"/>
            <interval number="3" begin_hour="" end_hour=""/>
        </day>
        <day number="5" worked_day="no">
            <interval number="1" begin_hour="08:30" end_hour="12:00"/>
            <interval number="2" begin_hour="13:30" end_hour="17:00"/>
            <interval number="3" begin_hour="" end_hour=""/>
        </day>
        <day number="6" worked_day="no">
            <interval number="1" begin_hour="" end_hour=""/>
            <interval number="2" begin_hour="" end_hour=""/>
            <interval number="3" begin_hour="" end_hour=""/>
        </day>
        <day number="7" worked_day="no">
            <interval number="1" begin_hour="" end_hour=""/>
            <interval number="2" begin_hour="" end_hour=""/>
            <interval number="3" begin_hour="" end_hour=""/>
        </day>
    </calendar>

如您所见，数据编码为UTF-8。

现在，我想解析这些数据以创建一些计算:

DECLARE @RawContent [nvarchar](MAX) = (
    SELECT wh.[CONTENT]
    FROM [WorkingHours] wh 
    WHERE wh.[ID] = 100);

DECLARE @XMLContent [Xml] = @RawContent; // KO
-- DECLARE @XMLContent [Xml] = CAST(@RawContent AS XML);  // KO
-- DECLARE @XMLContent [Xml] = CONVERT(XML, @RawContent); // KO

-- Just a test to query XML data.
SELECT 
    C.WD.value('@number', 'int') AS DayId         
FROM @XMLContent.nodes('/calendar/day') AS C(WD);   

我不知道如何将结果(包含 UTF-8 XML 字符串的 nvarchar(max) 字段)转换为 XML 值。SQL Server 返回以下错误:

"Unable to switch encoding"

它指的是 CAST 行(当我定义 @XMLContent 变量时)。

有解决办法吗？

Answer 1

删除处理指令——它没有意义且不正确，因为数据已经以 UTF-16 编码(因为它存储为 NVARCHAR)。如果你不能改变已经存在的数据，你将不得不依赖(稍微脆弱的)字符串替换:

CAST(REPLACE(wh.[CONTENT], '<?xml version="1.0" encoding="UTF-8"?>', '') AS XML)

请注意，显式指示编码为 UTF-16 也将起作用——尽管它不添加任何内容。