无法插入XML数据至表：日期时间转换失败求助（XML来自JavaScript）

哎，这个问题我太熟了！之前帮好几个朋友解决过类似的——核心就是明确指定日期转换的格式，再把你那种分号分隔的字符串拆成数据库能识别的单条记录就行，毕竟SQL Server默认的日期格式不一定适配你XML生成的MM/DD/YYYY HH:MI:SS带空格的时间串。具体操作如下：

1. 先明确表结构（假设你的XMLdata表是这样） #

首先得确保你的表字段和要插入的数据对应，比如：

CREATE TABLE XMLdata (
    Code VARCHAR(50),       -- 对应341300-02-1这类编号
    StartTime DATETIME,     -- 对应开始时间
    EndTime DATETIME,       -- 对应结束时间
    RecordID INT            -- 对应133072这类ID
);

2. 拆分字符串+转换日期插入 #

方法一：用STRING_SPLIT（SQL Server 2016及以上版本适用） #

这是最简便的方式，直接拆分分号和竖线分隔的内容，同时指定日期转换的格式：

-- 定义你的输入字符串
DECLARE @inputStr NVARCHAR(MAX) = '341300-02-1|04/10/2018 01:18:29|04/10/2018 06:18:29|133072; 261600-01-1|04/10/2018 06:18:29|04/10/2018 11:18:29|133073; 781100-R1-1|04/10/2018 11:18:29|04/10/2018 16:18:29|133074';

-- 插入数据到XMLdata表
INSERT INTO XMLdata (Code, StartTime, EndTime, RecordID)
SELECT
    -- 提取编号字段
    MAX(CASE WHEN FieldIndex = 1 THEN FieldValue END) AS Code,
    -- 转换开始时间，指定style=101（适配MM/DD/YYYY格式）
    CONVERT(DATETIME, MAX(CASE WHEN FieldIndex = 2 THEN FieldValue END), 101) AS StartTime,
    -- 转换结束时间
    CONVERT(DATETIME, MAX(CASE WHEN FieldIndex = 3 THEN FieldValue END), 101) AS EndTime,
    -- 转换记录ID为整数
    CAST(MAX(CASE WHEN FieldIndex = 4 THEN FieldValue END) AS INT) AS RecordID
FROM
    -- 第一步：拆分分号分隔的每条记录，过滤空值
    (SELECT TRIM(value) AS RecordValue FROM STRING_SPLIT(@inputStr, ';') WHERE TRIM(value) <> '') AS Records
-- 第二步：拆分每条记录里的竖线分隔字段，给字段加索引
CROSS APPLY
    (SELECT 
         value AS FieldValue,
         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS FieldIndex
     FROM STRING_SPLIT(Records.RecordValue, '|')) AS Fields
-- 按每条记录分组，聚合字段
GROUP BY Records.RecordValue;

方法二：兼容低版本SQL Server（无STRING_SPLIT） #

如果你的SQL Server版本低于2016，可以用XML拆分字符串的方式：

DECLARE @inputStr NVARCHAR(MAX) = '341300-02-1|04/10/2018 01:18:29|04/10/2018 06:18:29|133072; 261600-01-1|04/10/2018 06:18:29|04/10/2018 11:18:29|133073; 781100-R1-1|04/10/2018 11:18:29|04/10/2018 16:18:29|133074';

-- 把分号替换成XML节点，拆分每条记录
DECLARE @recordsXML XML = '<records><record>' + REPLACE(REPLACE(@inputStr, '; ', '</record><record>'), ';', '</record><record>') + '</record></records>';

INSERT INTO XMLdata (Code, StartTime, EndTime, RecordID)
SELECT
    -- 提取编号
    LEFT(record.value('.', 'NVARCHAR(MAX)'), CHARINDEX('|', record.value('.', 'NVARCHAR(MAX)')) - 1) AS Code,
    -- 转换开始时间
    CONVERT(DATETIME, SUBSTRING(record.value('.', 'NVARCHAR(MAX)'), CHARINDEX('|', record.value('.', 'NVARCHAR(MAX)')) + 1, 19), 101) AS StartTime,
    -- 转换结束时间
    CONVERT(DATETIME, SUBSTRING(record.value('.', 'NVARCHAR(MAX)'), CHARINDEX('|', record.value('.', 'NVARCHAR(MAX)'), CHARINDEX('|', record.value('.', 'NVARCHAR(MAX)')) + 1) + 1, 19), 101) AS EndTime,
    -- 提取记录ID
    CAST(RIGHT(record.value('.', 'NVARCHAR(MAX)'), LEN(record.value('.', 'NVARCHAR(MAX)')) - CHARINDEX('|', record.value('.', 'NVARCHAR(MAX)'), CHARINDEX('|', record.value('.', 'NVARCHAR(MAX)'), CHARINDEX('|', record.value('.', 'NVARCHAR(MAX)')) + 1) + 1)) AS INT) AS RecordID
FROM @recordsXML.nodes('/records/record') AS t(record)
WHERE TRIM(record.value('.', 'NVARCHAR(MAX)')) <> '';

关键注意点 #

• 日期格式适配：我用的style=101是适配MM/DD/YYYY格式（比如04/10/2018是4月10日），如果你的实际日期是DD/MM/YYYY（04/10/2018是10月4日），要把style改成103。
• 避免转换失败：如果怕有无效日期字符串，把CONVERT换成TRY_CONVERT，这样转换失败的记录会返回NULL，不会中断整个插入操作，方便后续排查问题。
• 空格不影响：XML生成的时间串里的空格完全没问题，SQL Server的日期转换函数能正常识别，不用特意去除。

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