使用SQL Server OpenXML读取含xsi:nil="true"的XML时无法识别NULL值报错

OPENXML配套sp_xml_preparedocument的旧版XML解析方案基于COM组件实现，默认不兼容W3C XML规范中xsi:nil="true"的空值标记逻辑，遇到带该属性的自闭合节点时，会尝试读取节点空内容做目标类型转换，对UNIQUEIDENTIFIER这类强类型字段会直接触发转换错误，无法自动映射为SQL NULL值。

方案1：使用原生XML类型解析（推荐） #

SQL Server 2005及以上版本提供的原生XML数据类型方法原生支持XML标准规范，无需依赖COM组件，也不需要手动释放内存资源，解析性能和稳定性都优于旧版OpenXML，实现代码如下：

WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT
    T.c.value('(Characters/text())[1]', 'NVARCHAR(50)') AS Characters,
    CASE WHEN T.c.exist('TimeLeaveId/@xsi:nil[.="true"]') = 1 
         THEN NULL 
         ELSE T.c.value('(TimeLeaveId/text())[1]', 'UNIQUEIDENTIFIER') END AS TimeLeaveId,
    CASE WHEN T.c.exist('TimeMissionId/@xsi:nil[.="true"]') = 1 
         THEN NULL 
         ELSE T.c.value('(TimeMissionId/text())[1]', 'UNIQUEIDENTIFIER') END AS TimeMissionId
FROM @TimeConvert.nodes('ArrayOfTimeConvertCreateVm/TimeConvertCreateVm') AS T(c)

该方案优势：

• 无需手动管理文档句柄，不存在内存泄漏风险
• 强类型解析容错性更高，适配各类标准XML格式
• 大体积XML场景下解析性能明显优于旧版OpenXML实现

方案2：兼容原有OpenXML逻辑改造 #

如果业务场景暂时无法重构原有OpenXML解析逻辑，可以在映射字段时额外读取xsi:nil属性值，手动判断返回NULL，注意解析完成后必须释放文档句柄：

DECLARE @handler INT;
EXEC sp_xml_preparedocument @handler OUT, @TimeConvert;

SELECT 
    Characters,
    CASE WHEN TimeLeaveId_IsNil = 1 THEN NULL ELSE TimeLeaveId END AS TimeLeaveId,
    CASE WHEN TimeMissionId_IsNil = 1 THEN NULL ELSE TimeMissionId END AS TimeMissionId
FROM
    OPENXML(@handler, 'ArrayOfTimeConvertCreateVm/TimeConvertCreateVm')
    WITH
    (
        [Characters] NVARCHAR(50) 'Characters',
        [TimeLeaveId] UNIQUEIDENTIFIER 'TimeLeaveId',
        [TimeLeaveId_IsNil] BIT 'TimeLeaveId/@xsi:nil',
        [TimeMissionId] UNIQUEIDENTIFIER 'TimeMissionId',
        [TimeMissionId_IsNil] BIT 'TimeMissionId/@xsi:nil'
    );

-- 强制释放COM组件占用的内存
EXEC sp_xml_removedocument @handler;

C#端使用XmlSerializer序列化实体类时，可以给可空字段标记[XmlElement(IsNullable = false)]特性，序列化null值时会直接省略对应XML节点，而非生成带xsi:nil="true"的自闭合节点，从生成端规避解析兼容问题。

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