SQL Server OpenXML函数用法 存储过程中解析XML参数

openxml使用必须先调用sp_xml_preparedocument获取句柄，否则报错；句柄为int型output变量，须配对sp_xml_removedocument防泄漏；需正确处理命名空间、flags标志位、xpath路径及xsinil空值映射。

OpenXML 在存储过程中必须先准备句柄

不调用 sp_xml_preparedocument 就直接用 OPENXML，sql Server 会报错：Invalid XML document. Document must be well-formed. 或更迷惑的 XML parsing: line N, character M, illegal xml character——其实根本没解析，是句柄为空。

关键点：句柄是整数变量，必须用 OUTPUT 获取，且后续必须配对调用 sp_xml_removedocument，否则内存泄漏（尤其高频调用时）。

• @hdoc 必须声明为 INT 类型，不能是 NULL 或未初始化值
• XML 字符串里不能含未转义的 &、、<code>>；传入前建议用 REPLACE 预处理或前端做实体编码
• 如果 XML 含命名空间（如 xmlns="http://example.com"），OPENXML 默认忽略所有元素——得在 sp_xml_preparedocument 第三个参数显式声明

OPENXML 的标志位（flags）决定数据映射方式

第三个参数 flags 控制节点如何转成行集：0=只取属性，1=只取子元素，2=属性+子元素混排。选错就查不到数据，而且毫无报错提示。

常见误用：把 XML 中的 <item id="123"><name>A</name></item> 当作元素来查，却用了 flags = 0，结果 name 列全为 NULL。

• flags = 0：只映射属性（@id 可取，name 元素内容不可见）
• flags = 1：只映射子元素（name 可取，@id 属性不可见）
• flags = 2：两者都映射（推荐起步值，但注意列名冲突）
• 若 XML 有嵌套结构（如 <order><items><item>...</item></items></order>），需分两步：先用 /order/items 定位父路径，再在 WITH 子句中用 item/name 引用子节点

WITH 子句字段名和 XPath 必须严格对应

WITH 里写的字段名不是别名，而是告诉 SQL Server “这个列的数据从哪个 XPath 路径提取”。写错路径或类型不匹配，字段就为 NULL，也不会报错。

例如 XML 是 <root><user><uid>100</uid><nick>Tom</nick></user></root>，想取 uid，WITH 写成 uid INT 'uid' 就失败——因为当前上下文是 /root/user，正确写法是 uid INT 'uid'（相对路径）或 uid INT './uid'，但绝不能写 '/root/user/uid'（绝对路径在 WITH 中无效）。

• 字符串字段建议显式指定长度，如 nick NVARCHAR(50) 'nick'，不写默认为 NVARCHAR(100)，可能截断
• 日期字段用 DATETIME 类型时，XML 中必须是 ISO 格式（2023-10-05T14:30:00），否则转成 NULL
• 如果某节点可能缺失，字段类型后加 XSINIL（如 email NVARCHAR(100) 'email' XSINIL），才能把空节点映射为 NULL 而非空字符串

性能与替代方案：OpenXML 不适合大 XML 或高频场景

每次调用 sp_xml_preparedocument 都触发 XML 解析和 dom 构建，1MB 以上 XML 就明显卡顿；并发高时还可能因句柄资源争用导致超时。

SQL Server 2005+ 原生支持 .nodes() 和 .value() 方法，语法更直观、内存占用低、执行计划更优。除非要复用同一份 XML 多次查询（这时 OpenXML 的句柄可省重复解析），否则优先用 XQuery 方式。

• 简单场景直接改用：select T.c.value('(uid/text())[1]', 'INT') AS uid FROM @xml.nodes('/root/user') T(c)
• OpenXML 的优势仅在：需要一次解析、多次不同结构查询；或必须兼容老版本（SQL Server 2000）
• 如果 XML 来自应用层且结构固定，更推荐在应用侧反序列化，数据库只存关系字段——避免把解析逻辑锁死在存储过程中

实际用 OpenXML 时，最容易漏掉的是 sp_xml_removedocument 调用，以及命名空间处理。这两处不出错，但数据永远对不上。