SQL存储过程结合XML数据类型的高性能解析技巧

直接用 .nodes() + .value()，别碰 OPENXML

从 SQL Server 2005 起，OPENXML 就应该被淘汰了。它需要手动调用 sp_xml_preparedocument 和 sp_xml_removedocument，一旦遗漏后者就会引发内存泄漏；而且整个过程基于临时内存树，无法利用 XML 索引，性能差且并发支持弱。

现代写法是直接使用原生 XML 方法：用 .nodes() 将 XML 拆解为行集，再通过 .value() 提取字段。这利用了引擎内置的解析器，支持 XML 索引下推，还能被查询优化器准确估算行数。

• @xml.nodes('/root/item') 路径必须指向元素节点（不能是文本或属性），否则返回空结果集
• 每个 .value() 必须携带 [1]，否则会报错“XQuery [value()]: ‘value()’ requires a singleton (or empty sequence)”
• 路径中使用 text() 显式提取文本值，例如 '(name/text())[1]'，不加则会返回带标签的 XML 片段，导致类型不匹配
• 如果某节点可能为空，.value() 会返回 NULL，无需额外 try-catch —— 但切忌在 WHERE 中直接写 .value() > 10，这样无法命中索引

WHERE 条件里优先用 .exist() 预筛，再用 .value() 提取

查询 XML 字段时，最常见的性能陷阱是将 .value() 直接放在 WHERE 中做比较：例如 WHERE content.value('(/book/price)[1]', 'DECIMAL') > 49.9。这会导致全表扫描，因为函数包裹列无法利用任何索引。

正确的做法是分两步：先用 .exist() 快速过滤出包含目标路径的行（可走 PATH 索引），再在结果集中用 .value() 精确提取值。

• WHERE content.exist('/book[price > 49.9]') = 1 —— 注意 XPath 中不能直接使用 >，必须用实体编码 >，SQL Server 不支持原生比较符
• 如果需要参数化，使用 sql:variable("@minPrice")，例如 content.exist('/book[price > sql:variable("@minPrice")]')
• .exist() 在 XML 列为 NULL 时返回 NULL，因此实际条件建议写成 IS NOT NULL AND ... = 1 更稳妥

建 XML 索引前，必须先建主 XML 索引

想要让 .exist()、.value() 或 .query() 走索引，不能直接建立次级索引。XML 索引是分层结构：主索引（PRIMARY）是聚集索引，它将 XML 内部节点展开成系统表；所有次级索引（PATH/VALUE/PROPERTY）都依赖它而存在。

漏建主索引，或者主索引被禁用，次级索引就会形同虚设——执行计划中依然会显示“Table Scan”。

• 主索引语法：CREATE PRIMARY XML INDEX IX_primary ON docs(content)
• PATH 索引加速路径查找（如 /book/title），适合 .exist() 和带明确路径的 .value()
• VALUE 索引加速通配查找（如 //price），适合模糊路径或深层嵌套场景
• 一个 XML 列上最多只能建一个主索引和三个次级索引；索引本身占用空间大、写入开销高，只对高频查询字段建立

类型化 XML 能省掉部分类型转换，但别指望它自动提速

如果数据结构稳定、有 XSD 定义，注册 Schema Collection 并绑定到 XML 列，就能启用类型化 XML。它的主要价值有两点：一是插入时进行强校验，将坏数据拦截在门外；二是 .value() 中某些类型可省略声明，例如 xs:integer 属性可以自动映射为 SQL INT。

但它不会让查询变快——索引行为、执行计划、IO 开销与非类型化 XML 完全一致。类型信息只影响解析阶段的类型推断，不改变底层存储结构或索引机制。

• 非类型化 XML 的所有值默认按字符串处理，.value('(@id)', 'INT') 必须显式指定类型，否则会报错
• 类型化 XML 中，如果 XSD 已声明 @id 为 xs:integer，可以简写为 .value('(@id)', 'INT') 甚至 .value('(@id)')（引擎会尝试推断）
• XSD 解析本身有 CPU 开销，写入吞吐量比非类型化低 10%~20%，日志类、配置类数据没必要使用类型化

复杂点在于：XML 索引并非“建了就快”，而是“建对了才快”。PATH 索引对 /a/b/c 有效，对 //c 无效；VALUE 索引对 //c 有效，但对 /a/b/c 效率反而不如 PATH。路径写法、索引选型、是否类型化，必须根据实际查询模式逐一匹配，无法一劳永逸。