MS SQL解析含<、>符号的XML出错，求正确存储数据的方法

当XML内容包含未转义的<或>时，MS SQL尝试将其转换为XML类型会直接报错——比如你提供的示例XML中，<name>标签内的Auto < Test或AutoTest!@$^&(<>)|;%#~Org.这类内容，会破坏XML的合法性，导致解析失败。

你的现有代码仅处理了&和"的转义，未覆盖<和>，因此无法解决解析错误。以下是两种可行的解决方案：

方案1：使用CDATA段包裹内容（推荐） #

CDATA段允许XML中包含任意特殊字符（除了]]>），解析器会将其视为纯文本处理，无需手动转义。只需将原始XML的内容标签替换为包含CDATA的格式即可：

DECLARE @OrgNames NVARCHAR(MAX)= N'<root>
                                        <name>Auto < Test</name>
                                        <name>AutoTest!@$^&(<>)|;%#~Org.</name>
                                    </root>'

-- 将<name>替换为<name><![CDATA[，将</name>替换为]]></name>
DECLARE @ValidXMLStr NVARCHAR(MAX) = REPLACE(REPLACE(@OrgNames, '<name>', '<name><![CDATA['), '</name>', ']]></name>')

-- 转换为合法XML并解析
DECLARE @columnStr_XML XML = CAST(@ValidXMLStr AS XML)

-- 提取内容并插入临时表
SELECT f.x.value('.', 'NVARCHAR(MAX)') AS name,
       CAST(NULL AS BIGINT) AS org_id
INTO #temp_name
FROM @columnStr_XML.nodes('/root/name') f(x)
WHERE f.x.value('.', 'NVARCHAR(MAX)') <> ''

-- 直接查询即可得到原始内容，无需反向替换
SELECT * FROM #temp_name

DROP TABLE #temp_name

方案2：手动转义特殊字符 #

如果无法使用CDATA，可以手动转义内容中的<和>，但需注意不能转义XML标签本身的<和>，因此需要先处理原始字符串中的特殊字符，再还原标签的合法格式：

DECLARE @OrgNames NVARCHAR(MAX)= N'<root>
                                        <name>Auto < Test</name>
                                        <name>AutoTest!@$^&(<>)|;%#~Org.</name>
                                    </root>'

-- 先转义所有特殊字符：&->&amp;，<->&lt;，>->&gt;，"->&quot;
DECLARE @ValidXMLStr NVARCHAR(MAX) = 
    REPLACE(REPLACE(REPLACE(REPLACE(@OrgNames, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '"', '&quot;')

-- 还原XML标签的合法格式
SET @ValidXMLStr = REPLACE(REPLACE(@ValidXMLStr, '&lt;root&gt;', '<root>'), '&lt;/root&gt;', '</root>')
SET @ValidXMLStr = REPLACE(REPLACE(@ValidXMLStr, '&lt;name&gt;', '<name>'), '&lt;/name&gt;', '</name>')

-- 转换为XML并解析
DECLARE @columnStr_XML XML = CAST(@ValidXMLStr AS XML)

-- 提取内容
SELECT f.x.value('.', 'NVARCHAR(MAX)') AS name,
       CAST(NULL AS BIGINT) AS org_id
INTO #temp_name
FROM @columnStr_XML.nodes('/root/name') f(x)
WHERE f.x.value('.', 'NVARCHAR(MAX)') <> ''

SELECT * FROM #temp_name

DROP TABLE #temp_name

方案对比 #

• 方案1（CDATA）更简洁，无需区分标签和内容，适合大多数场景；
• 方案2适合无法修改XML标签结构的场景，但需要额外还原标签的转义，维护成本较高。

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