SQL Server XML解析报错（缺分号）及@InputData批量数据处理咨询

首先咱们拆解下这个错误：你遇到的「第1行，第2923个字符，预期分号」XML解析报错，大概率是两个原因导致的：

• 你的@InputData字符串里存在XML特殊字符（比如&、<、>、"、'），XML解析器会把这些字符当成XML语法的一部分，从而触发错误；
• 你提供的@InputData末尾带有...，这会导致用于生成XML的字符串格式不完整，解析器找不到预期的分隔符或闭合标记。

下面给你两种针对性的处理方案：

方案1：清理输入并转义XML特殊字符 #

如果你的需求是把这个分隔字符串转换成XML来处理，首先要确保输入格式完整，然后转义XML敏感字符：

DECLARE @InputData NVARCHAR(MAX) 
SET @InputData = '5B 71-00-01 DISSA 001,03/26/2018 15:50:08,03/26/2018 16:50:08,41871;5B 71-00-01 DISSA 002,03/26/2018 16:50:08,03/26/2018 17:50:08,41872;5B 71-00-01 DISSA 003,03/26/2018 17:50:08,03/26/2018 18:50:08,41873;5B 71-00-01 DISSA 004,03/26/2018 18:50:08,03/26/2018 19:50:08,41874;5B 71-00-01 DISSA 005,03/26/2018 19:50:08,03/26/2018 20:50:08,41875;5B 71-00-01 DISSA 006,03/26/2018 20:50:08,03/26/2018 21:50:08,41876;5B 71-00-01 DISSA 007,03/26/2018 21:50:08,03/26/2018 22:50:08,41877'

-- 第一步：转义XML特殊字符，避免解析报错
SET @InputData = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@InputData, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '"', '&quot;'), '''', '&apos;')

-- 第二步：构建合法的XML结构
DECLARE @XmlData XML
SET @XmlData = CAST('<Records><Record><Value>' + REPLACE(REPLACE(@InputData, ';', '</Value></Record><Record><Value>'), ',', '</Value><Value>') + '</Value></Record></Records>' AS XML)

-- 验证XML是否可以正常查询
SELECT 
    t.c.value('Value[1]', 'NVARCHAR(100)') AS ItemName,
    t.c.value('Value[2]', 'DATETIME') AS StartTime,
    t.c.value('Value[3]', 'DATETIME') AS EndTime,
    t.c.value('Value[4]', 'INT') AS ID
FROM @XmlData.nodes('/Records/Record') t(c)

方案2：跳过XML，直接拆分分隔字符串（更简单） #

如果你的核心需求是把这个分号+逗号分隔的字符串转换成表格数据，其实可以不用XML，直接用字符串拆分函数（SQL Server 2016+支持STRING_SPLIT），完全规避XML解析的问题：

DECLARE @InputData NVARCHAR(MAX) 
SET @InputData = '5B 71-00-01 DISSA 001,03/26/2018 15:50:08,03/26/2018 16:50:08,41871;5B 71-00-01 DISSA 002,03/26/2018 16:50:08,03/26/2018 17:50:08,41872;5B 71-00-01 DISSA 003,03/26/2018 17:50:08,03/26/2018 18:50:08,41873;5B 71-00-01 DISSA 004,03/26/2018 18:50:08,03/26/2018 19:50:08,41874;5B 71-00-01 DISSA 005,03/26/2018 19:50:08,03/26/2018 20:50:08,41875;5B 71-00-01 DISSA 006,03/26/2018 20:50:08,03/26/2018 21:50:08,41876;5B 71-00-01 DISSA 007,03/26/2018 21:50:08,03/26/2018 22:50:08,41877'

-- 先拆分分号分隔的每条记录，再拆分逗号分隔的字段
SELECT 
    PARSENAME(REPLACE(value, ',', '.'), 4) AS ItemName,
    CAST(PARSENAME(REPLACE(value, ',', '.'), 3) AS DATETIME) AS StartTime,
    CAST(PARSENAME(REPLACE(value, ',', '.'), 2) AS DATETIME) AS EndTime,
    CAST(PARSENAME(REPLACE(value, ',', '.'), 1) AS INT) AS ID
FROM STRING_SPLIT(@InputData, ';')

关键注意点： #

• 一定要确保@InputData没有末尾的...，保证每条记录都是完整的分号分隔；
• 如果你的SQL Server版本低于2016，不能用STRING_SPLIT，可以自己写一个自定义的字符串拆分函数，或者用方案1的XML方法但必须先转义特殊字符。

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