OPENXML + sp_xml_preparedocument 是 SQL Server 中解析 XML 最可控稳定的方式,需先调用 sp_xml_preparedocument 获取句柄,再用 OPENXML 配合 WITH 子句提取字段,最后清理句柄;深层嵌套推荐 nodes() + CROSS APPLY + .value() 组合。

直接用 OPENXML + sp_xml_preparedocument 是最可控、最稳定的方式,尤其在 SQL Server 2022 中仍完全支持且性能可靠;别依赖 SELECT ... FROM OPENXML() 的隐式解析,它对嵌套层级和命名冲突极其敏感。
为什么不用 FOR XML 反向“解析”?
FOR XML 是单向生成工具,它只负责把关系数据“折叠”成 XML,不提供反向展开能力。有人误以为用 SELECT * FROM OPENROWSET(BULK) 加 XML 格式化文件能还原结构,但那只是批量导入时的列映射机制,无法处理任意嵌套路径或动态节点名。真正要从 XML 字符串里抽字段,必须走 XML 解析流程。
OPENXML 前必须调用 sp_xml_preparedocument
这是最容易漏掉的关键步骤——sp_xml_preparedocument 把 XML 字符串编译成内存中的内部树结构,后续 OPENXML 才能按 XPath 定位。漏掉这步会直接报错 Msg 6605, Level 16。
- 返回值是
@hdoc句柄(int类型),必须保存并在最后用sp_xml_removedocument @hdoc清理,否则内存泄漏 - XML 中含命名空间时,必须在
sp_xml_preparedocument第二个参数传入N'xmlns:ns="http://example.com/ns"',否则//ns:Item这类路径全失效 - SQL Server 2022 默认启用
xml data type的严格验证,若 XML 有 DTD 或 schema 引用但不可达,sp_xml_preparedocument会静默失败或抛Msg 6602
用 OPENXML 指定 flag 控制扁平化粒度
OPENXML 的第三个参数 flag 决定节点如何映射到结果集:
-
1(XML_ELEMENTS):所有字段作为子元素值,适合纯内容提取,但无法区分同名不同层的<name></name> -
2(XML_ATTRIBUTES):只取属性值,忽略子元素,适合解析<order id="123"></order>这类结构 -
8(XML_SCHEMA):生成带类型信息的结果集,但 SQL Server 2022 中已不推荐,类型推断常出错 - 实际常用组合是
2 | 8或1 | 8,但务必配合显式WITH子句定义列名和类型,否则默认全为nvarchar(100),数字/日期全变字符串
示例:提取 <order><customer><name>Alice</name></customer><items><item qty="2">Book</item></items></order> 中的客户名和数量属性:
DECLARE @hdoc int; EXEC sp_xml_preparedocument @hdoc OUTPUT, N'<Order><Customer><Name>Alice</Name></Customer><Items><Item qty="2">Book</Item></Items></Order>'; <p>SELECT CustomerName, ItemQty FROM OPENXML(@hdoc, '/Order/Customer', 2) WITH ( CustomerName nvarchar(50) 'Name', ItemQty int '../Items/Item/@qty' );</p><p>EXEC sp_xml_removedocument @hdoc;
嵌套多层时避免手动拼接,改用 CROSS APPLY + nodes()
当 XML 层级深(比如 /Root/Orders/Order/Details/Detail)、且需保留父子关联时,OPENXML 的 WITH 子句写法极易失控。此时更推荐 nodes() 方法:
-
.nodes()返回的是行集,每个匹配节点一行,天然支持多级展开 - 用
CROSS APPLY逐层下钻,比如先Orders.nodes('/Root/Orders/Order'),再对每行.nodes('Details/Detail') - 字段提取用
.value(),可指定 XPath 和类型,如T.c.value('(./@id)[1]', 'int'),比OPENXML的模糊路径更精准 - 注意
.value()要求表达式返回单个值,所以必须加[1],否则报Msg 2389
这种写法在 SQL Server 2022 中性能更好,且能自然处理空节点(返回 NULL 而非报错),但要注意:如果某层节点完全缺失(比如某个 Order 没有 Details),CROSS APPLY 会丢掉整条 Order 记录——这时得换 OUTER APPLY。
复杂点在于命名空间和空值处理:XML 中的 xsi:nil="true" 不会被自动转成 NULL,必须在 .value() 后手动判断;而跨多级回溯路径(如 ../@order_id)在深层嵌套中容易因上下文丢失失效——这些细节不调试根本发现不了。

















