编程语言研究书库
SQL Server XML 与 JSON 实例

拆分 XML(上)— OPENXML 的老 mechanics 与 XQuery 的接班

拆分 XML(上)— OPENXML 的老 mechanics 与 XQuery 的接班

这一章讲什么: shredding(拆分)——第 01 章定义过,把 XML 转换成行列格式——的 第一代工具 OPENXML 怎么转,以及为什么 2005 年后该改用 XQuery。 原书第 4 章有八个配方,本章拆前两个(OPENXML 本体 + 迁移到 XQuery), 后六个(列上拆分、遗留存储、类型化 XML、query()、嗅探、多级 CROSS APPLY)在下一篇。

1. 先看现象:一份制造指令,怎么变成一张表

AdventureWorks 的 Production.ProductModel 表有一列 Instructions,存的是制造指令 XML——哪个工位(LaborStation)、批量(LotSize)、几个工时(LaborHours),每道工序( step)用什么材料(material)、什么工具(tool)1。大概长这样:

<root xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelManuInstructions">
  <Location LocationID="10" LaborHours="3" LotSize="100" MachineHours="3" SetupHours="0.5">
    <step>…aluminum sheet MS-2341…<material>aluminum sheet MS-2341</material><tool>…</tool></step>
    …(同一工位下有多道 step,数量不固定)
  </Location>
</root>

(骨架来自原书 Listing 4-3,原文太长书页印不下,这是演示节选。)

本章主线例子: 把这份 XML 拆成一张表——每一行是一道工序,列出它的工位号、工时、材料、工具。表里 LocationID="10" 这个工位在这个例子里有 6 道工序,而工序数随产品型号变化2。拆分的结果应该长这样:

StepInstructionLaborStationLaborHoursLotSizeMachineHoursSetupHoursMaterialTool
(第1道工序文本)10310030.5aluminum sheet MS-2341…
…(共 6 行)

目标定了。两代工具争的就是「怎么走到这张表」。

2. 顶层全景:两代工具的形状

  第一代 OPENXML(2000 年,XML 类型还不存在):
    文本 → sp_xml_preparedocument → 内存里的文档 + 数字句柄 @idoc
        → FROM OPENXML(@idoc, 'XPath', 标志) WITH (列定义…)
        → sp_xml_removedocument @idoc   ← 漏掉这步 = 内存泄漏

  第二代 XQuery(2005 年起,XML 类型原生支持):
    XML 值 .nodes('XPath') 表别名(列别名)   ← nodes() 直接定行,不要句柄
        → 列别名.value('路径[1]', '类型') AS 列名

  图说:第一代的「句柄 + 释放」整套仪式,在第二代里消失了——XML 类型自己就是数据源。

3. 核心原理

3.1 OPENXML 的三段仪式:prepare、shred、remove

解决什么问题: 2000 年的 SQL Server 还没有 XML 数据类型,XML 只是 TEXT/NTEXT 一类的长字符串。要拆它,得先让 MSXML 解析器把字符串解析成内存里的文档树。

怎么做,三段仪式:

仪式一,prepare(备料):EXEC sp_xml_preparedocument @doc OUTPUT, @xml, @ns。系统存储过程用 MSXML 解析器处理 XML,返回一个整数句柄(document handle)——一个指向内存文档的指针,存在 @doc 变量里3。三个参数:输出句柄、XML 文本(必须能隐式转成老式 NTEXT,所以 VARBINARY 不收)、可选的命名空间声明4。

仪式二,shred(开拆):查询的 FROM 子句里放 OPENXML(@idoc, 'XPath行模式', 标志),后面跟 WITH 构造定义输出表的形状(列名、类型、每项在 XML 里的路径)5。

仪式三,remove(善后):EXEC sp_xml_removedocument @idoc。这一步不是礼仪,是保命:SQL Server 不为这些文档做垃圾回收,解析器缓存最多能吃掉 SQL Server 总内存的八分之一;不显式释放就是内存泄漏,服务器会被迫周期性重启6。

3.2 命名空间:绕不过去的那道关

主线例子的 <root> 带着一个命名空间(那一长串 http://schemas.microsoft.com/…)。书里把话说死了:XML 带命名空间时,命名空间无法回避——不声明、不在 sp_xml_preparedocument 里指定,OPENXML 不会报错,而是默默地忽略所有元素,返回空结果集7。

一个容易懵的细节:XML 文档里声明是 xmlns="…"(默认命名空间),而传给存储过程的 @ns 变量要写成 xmlns:df="…" 的形式——给命名空间起一个别名(df 是 "default" 的缩写,你也可以用 ns)。书里给的类比很准:这就像查表必须写 Production.ProductModel 而不是裸表名——不写架构名,SSMS 报「object not found」;XML 这边不报错,只是查无结果,更阴8。

3.3 行模式、@flags 与 WITH 里的导航:../ 和 @

行模式(@rowpattern): OPENXML 的第二个参数是 XPath 路径,指明「一行对应 XML 里的哪个元素」。定它的规则,书里给了一条作者经验:同一个子元素出现多次时,行模式必须指向那个元素——本例中 step 在 Location 下重复出现,所以行模式是 root/Location/step9。

@flags(映射标志): 第三个参数告诉 OPENXML 数据按元素中心还是属性中心映射。值 1=属性中心,2=元素中心,8=可和 1/2 组合、把多余数据不复制到溢出属性。本例选 2:终点 step 是元素,而它的父级 Location 的属性要进结果集——元素中心 + 顺手收父级属性,这是 flags=2 的最佳场景10。

WITH 构造里的导航: WITH 给输出表逐列定义「去哪取值」。难点在于值常常不在行模式指的那个元素上,要会两种走法:

写法意思本例
'../@LocationID'../ 上一级,@ 属性工位号是 Location 的属性,而行模式停在 step——得先上一级
'df:material'向下的子元素(带命名空间别名)material 是 step 的孩子,直接往下
'.'当前节点自己step 的文本内容就是工序描述

「先上一级」的理由书里讲得很形象:XML 层级像 Windows 文件夹——文件散在不同文件夹里,你得一级级导航去收11。SELECT 列名就是 WITH 里起的别名(StepInstruction、LaborStation…)12。

3.4 迁移到 XQuery:少掉的是仪式,多出来的是三个坑

解决什么问题: OPENXML 的整套仪式(句柄、释放、MSXML 缓存)又重又危险,2005 年 XML 类型诞生后理应淘汰。

怎么做: 同样的主线例子,XQuery 版只有两行核心(原书 Listing 4-10,有省略):

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as df)
SELECT instruct.value('.', 'VARCHAR(2000)') AS StepInstruction,
       instruct.value('../@LocationID', 'int') AS LaborStation,
       …
       instruct.value('df:material[1]', 'VARCHAR(100)') AS Material,
       instruct.value('df:tool[1]', 'VARCHAR(100)') AS Tool
FROM @xml.nodes('df:root/df:Location/df:step') prod(instruct);

零件对照表——这是本章最值钱的一张表13:

环节OPENXMLXQuery
数据源入口OPENXML(@doc, 路径, flags),要先备料XML.nodes(路径) 表(列) 别名,直接来
文档句柄 @doc必须不需要
映射标志 flags必须想清楚不存在这个概念
输出形状WITH(别名 类型 路径)value(路径, 类型) AS 别名——顺序整个倒过来
命名空间声明sp_xml_preparedocument 的第三参数WITH XMLNAMESPACES 子句

为什么有效: XML 类型本身就是解析好的树,不需要「先解析成内部格式」那一步;XQuery 语言直接工作在树上。

迁移后必踩的三个新坑(书里逐一点名):

  1. 单例下标 [1]: value() 方法要求返回单值(singleton),元素引用必须带从 1 开始的下标:df:material[1]。现代编程语言数组大多从 0 开始,这里的 [1] 是第一个——而且漏写就报错(Msg 2389:requires a singleton)14。为什么要下标?因为同一个元素名可以出现多次(三个 <Address> 就要 Address[1]、Address[2]、Address[3] 三列)15。
  2. 全小写: nodes()、value() 等方法名必须全小写——T-SQL 不挑 ISNULL 的大小写,惯坏了我们;XML 这边挑,Nodes() 直接报错16。
  3. WITH XMLNAMESPACES 前面要有分号:它前面的语句必须以 ; 收尾,否则报错17。

别名规矩: nodes() 的别名必须是两段式 表(列) 格式,如 prod(instruct)。作者自用简写 T(C),但建议生产环境用更有信息量的 Tbl(Clmn)18。

3.5 CONVERT 的 XML 样式参数:一个顺带的硬核知识点

把字符串列 CAST/CONVERT 成 XML 类型时,CONVERT 有一个 CAST 没有的第三参数——XML 样式:0(默认,丢弃无意义空白、不允许内部 DTD)、1(保留空白)、2(丢空白 + 有限 DTD)、3(保空白 + 有限 DTD)19。CAST 是 ANSI 标准语法,CONVERT 是微软方言但功能多——这是两者唯一的实质差别20。什么时候在乎?XML 里含内部 DTD(如 ENTITY 声明,配方 4-1 的标题就提到它)或要逐字节保留空白时。

4. 作者的判断与证据

有代码级证据的: OPENXML 的内存泄漏机制(MSXML 缓存八分之一内存上限、必须显式 remove)6;命名空间不声明返回空集而非报错7;value() 缺 [1] 的 Msg 2389、方法名大写的报错,书里都给了真实错误号和原文1416。

作者的经验判断: 「自动生成 Schema 的时代结束了」那一脉立场,延伸到「OPENXML 已过时,该迁」——书里措辞是 sp_xml_preparedocument / sp_xml_removedocument 对 XML 类型obsolete(已废弃)、unused(无人用)21。严格说这是立场:遗留系统里 OPENXML 仍大量存在,不然也不会有这个「迁移」配方。

5. 边界与局限

  • 本章只走到「单文档拆分」: 对表的一整列批量拆分、类型化 XML 的特殊报错、query() 取子集,都在下一篇(06 章)。
  • OPENXML 的更多能力没展开: 元属性(@mp:xmltext 溢出列等)只在 flags 表里露了一面;真维护遗留代码时,官方文档的 OPENXML 一节仍要读(补充,不在书里,来自通用知识)。
  • XPath 本身没教: 行模式怎么写依赖 XPath 基础,原书假设读者会;不会的话,任何 XPath 教程的「路径表达式」一章是前置阅读(补充,不在书里)。

6. 可带走的

本章主线例子,一行走完: 制造指令 XML(工位 10,6 道工序)→ OPENXML 三仪式(备料拿句柄 → OPENXML+WITH 拆 → remove 释放)→ 或 XQuery 两行(nodes('df:root/df:Location/df:step') 定行 → value() 逐列取值,父级属性用 ../@)。

  1. OPENXML = 备料(sp_xml_preparedocument 拿句柄)→ 拆(OPENXML+WITH)→ 释放(sp_xml_removedocument),漏释放 = 内存泄漏,解析器缓存能吃总内存的八分之一6。
  2. 命名空间无法回避:不声明不报错,返回空集——比报错更坑7。
  3. WITH 里 ../ 上一级取父属性、@ 取属性、. 取当前文本11。
  4. flags=2(元素中心)是「终点是元素、要收父级属性」场景的默认选择10。
  5. 新代码用 XQuery:nodes() 定行 + value() 取值,没有句柄没有释放21。
  6. value() 的三个新坑:元素必须 [1] 下标、方法名全小写、WITH XMLNAMESPACES 前要有分号141617。
  7. 别名两段式 表(列);生产环境别用 T(C)18。
  8. CONVERT 比 CAST 多一个 XML 样式参数(0–3),管空白和内部 DTD19。

7. 原文地图

主题原书章原文位置
制造指令 XML 与行模式分析Chapter 4: Shredding XMLtext/08-ch04-chapter-4-shredding-xml.txt:67(搜「ProductModelID = 7」) · text/08-ch04-chapter-4-shredding-xml.txt:204(搜「listed more than once」)
命名空间不可回避与 df 别名同上text/08-ch04-chapter-4-shredding-xml.txt:97(搜「cannot be avoided」) · text/08-ch04-chapter-4-shredding-xml.txt:120(搜「extra part」)
sp_xml_preparedocument 三参数同上text/08-ch04-chapter-4-shredding-xml.txt:134(搜「MSXML parser」)
OPENXML 三参数与 flags 表同上text/08-ch04-chapter-4-shredding-xml.txt:168(搜「three input parameters」) · text/08-ch04-chapter-4-shredding-xml.txt:181(搜「attribute-centric mapping」)
WITH 构造的导航(../、@、.)同上text/08-ch04-chapter-4-shredding-xml.txt:227(搜「folder structure」) · text/08-ch04-chapter-4-shredding-xml.txt:247(搜「Move one level up」)
内存泄漏警告同上text/08-ch04-chapter-4-shredding-xml.txt:290(搜「garbage collection」)
OPENXML 过时、XQuery 接班同上text/08-ch04-chapter-4-shredding-xml.txt:303(搜「XML data type methods: nodes()」)
WITH XMLNAMESPACES 与分号同上text/08-ch04-chapter-4-shredding-xml.txt:368(搜「WITH XMLNAMESPACES」)
nodes() 与 OPENXML 对照同上text/08-ch04-chapter-4-shredding-xml.txt:410(搜「similar to the OPENXML」)
WITH 与 value() 顺序相反同上text/08-ch04-chapter-4-shredding-xml.txt:433(搜「all the reverse」)
单例 [1] 与 Msg 2389同上text/08-ch04-chapter-4-shredding-xml.txt:451(搜「singleton atomic value」) · text/08-ch04-chapter-4-shredding-xml.txt:471(搜「Msg 2389」)
方法名全小写同上text/08-ch04-chapter-4-shredding-xml.txt:593(搜「normally, whereas」)
别名两段式 T(C)同上text/08-ch04-chapter-4-shredding-xml.txt:425(搜「fully qualified alias」)
CONVERT XML 样式参数同上text/08-ch04-chapter-4-shredding-xml.txt:340(搜「third optional parameter」)

Footnotes

  1. 出处:「Chapter 4: Shredding XML」第 63–83 段(text/08-ch04-chapter-4-shredding-xml.txt:67,搜「ProductModelID = 7」)。示例数据取自 SELECT Instructions FROM Production.ProductModel WHERE ProductModelID = 7;「The XML result is too large to display on a book page」,Listing 4-3 是格式化节选。 ↩

  2. 出处:「Chapter 4: Shredding XML」第 203–217 段(text/08-ch04-chapter-4-shredding-xml.txt:204,搜「listed more than once」)。原文:「When the same child element is listed more than once, the XPath pattern must point to that element」;「the LaborStation attribute (property of the Location element) with value "10" has 6 steps in this particular example; however, the number of steps can vary」。 ↩

  3. 出处:「Chapter 4: Shredding XML」第 134–136 段(text/08-ch04-chapter-4-shredding-xml.txt:134,搜「MSXML parser」)。原文:「The system stored procedure sp_xml_preparedocument uses the MSXML parser (Msxmlsql.dll) to parse the XML data and returns a numeric (INT data type) value that provides a pointer (variable @doc) to access the XML」。 ↩

  4. 出处:「Chapter 4: Shredding XML」第 137–147 段(text/08-ch04-chapter-4-shredding-xml.txt:137,搜「three parameters」)。三个参数:输出句柄 INT、「@xmltext 可做任何文本类型(VARCHAR, NVARCHAR, XML, TEXT, or NTEXT)…VARBINARY is not allowed」、可选命名空间参数。 ↩

  5. 出处:「Chapter 4: Shredding XML」第 158–165 段(text/08-ch04-chapter-4-shredding-xml.txt:159,搜「three parts」)。原文:「SELECT clause – Delivers the result」「OPENXML function – Provides access to the XML document and sets the XPath to the startup element」「WITH construct – Defines the table that describes each element and attribute」。 ↩

  6. 出处:「Chapter 4: Shredding XML」第 290–296 段(text/08-ch04-chapter-4-shredding-xml.txt:290,搜「garbage collection」)。原文:「SQL Server does not provide garbage collection for XML documents processed by the sp_xml_preparedocument stored procedure」「the MSXML parser uses one-eighth of the total memory available for SQL Server」「deallocation must be set explicitly…Otherwise, the server will have a memory leak problem」。 ↩ ↩2 ↩3

  7. 出处:「Chapter 4: Shredding XML」第 97–99 段(text/08-ch04-chapter-4-shredding-xml.txt:97,搜「cannot be avoided」)。原文:「When XML has a namespace, the namespace cannot be avoided」「the OPENXML function will not return the result set when the namespace is not declared」。 ↩ ↩2 ↩3

  8. 出处:「Chapter 4: Shredding XML」第 120–127 段(text/08-ch04-chapter-4-shredding-xml.txt:120,搜「extra part」)。原文:「The value of the variable @ns has an extra part xmlns:df=…」「it's the same reference as if the ProductModel table were referenced without the schema "Production."」「the XML parser will not raise an error and will simply ignore the elements and their attributes. Therefore, no results will be returned」。 ↩

  9. 出处:「Chapter 4: Shredding XML」第 203–206 段(text/08-ch04-chapter-4-shredding-xml.txt:204,搜「listed more than once」)。原文:「When the same child element is listed more than once, the XPath pattern must point to that element」「The hierarchy for the <step> element is: root/Location/step」。 ↩

  10. 出处:「Chapter 4: Shredding XML」第 177–184 段(text/08-ch04-chapter-4-shredding-xml.txt:181,搜「attribute-centric mapping」)与第 218–221 段(text/08-ch04-chapter-4-shredding-xml.txt:221,搜「best scenario」)。flags:1=属性中心、2=元素中心、8=位组合不复制溢出;本例:「This combination of element-centric and attribute-centric properties is the best scenario for @flags = 2」。 ↩ ↩2

  11. 出处:「Chapter 4: Shredding XML」第 226–231 段(text/08-ch04-chapter-4-shredding-xml.txt:227,搜「folder structure」)。原文:「The hierarchical structure of XML data can be compared to the Windows folder/file structure…you are navigating from one folder to another to collect all needed files」。 ↩ ↩2

  12. 出处:「Chapter 4: Shredding XML」第 266–282 段(text/08-ch04-chapter-4-shredding-xml.txt:268,搜「StepInstruction」)。SELECT 列即 WITH 别名:StepInstruction(step 文本)、LaborStation(LocationID 别名)、LaborHours、LotSize、MachineHours、SetupHours、Material、Tool。 ↩

  13. 出处:「Chapter 4: Shredding XML」第 408–449 段(text/08-ch04-chapter-4-shredding-xml.txt:410,搜「similar to the OPENXML」)。nodes() 与 OPENXML 的对照:「XQuery expects the XML data type; therefore, the XML handler (@doc variable) is not needed」「The mapping @flags parameter is not used by the nodes() method」。 ↩

  14. 出处:「Chapter 4: Shredding XML」第 450–453 段(text/08-ch04-chapter-4-shredding-xml.txt:451,搜「singleton atomic value」)。原文:「the value() method requires a singleton atomic value, indicated by a one-based index reference ("[1]")」。 ↩ ↩2 ↩3

  15. 出处:「Chapter 4: Shredding XML」第 455–474 段(text/08-ch04-chapter-4-shredding-xml.txt:455,搜「one-based array index」)。原文:「That provides the ability for the XML data to list the same element name multiple times」,三个 Address 的取法 Address[1]/[2]/[3];漏下标报错:「Msg 2389, Level 16, State 1…'value()' requires a singleton (or empty sequence)」。 ↩

  16. 出处:「Chapter 4: Shredding XML」第 575–596 段(text/08-ch04-chapter-4-shredding-xml.txt:593,搜「normally, whereas」)。原文:「FROM @x.nodes('StoreSurvey') survey(details) executes normally, whereas FROM @x.Nodes('StoreSurvey') survey(details) raises an error when "Nodes()" function is capitalized」;并对比「SQL Server T-SQL does not raise any errors when we type the function ISNULL()…in lower, upper, or mixed cases」。 ↩ ↩2 ↩3

  17. 出处:「Chapter 4: Shredding XML」第 367–377 段(text/08-ch04-chapter-4-shredding-xml.txt:368,搜「WITH XMLNAMESPACES」)。原文:「The SQL Server WITH XMLNAMESPACES clause allows us to list and instantiate the XML namespaces」「Always make sure that a semicolon (;) precedes the WITH construct」。 ↩ ↩2

  18. 出处:「Chapter 4: Shredding XML」第 424–428 段(text/08-ch04-chapter-4-shredding-xml.txt:425,搜「fully qualified alias」)。原文:「the nodes() method requires a fully qualified alias, such as table(column)…I use the T(C) alias…in a production environment, I would recommend being more specific than T(C); for example, Tbl(Clmn)」。 ↩ ↩2

  19. 出处:「Chapter 4: Shredding XML」第 343–352 段(text/08-ch04-chapter-4-shredding-xml.txt:340,搜「third optional parameter」)。四个样式值 0/1/2/3 的定义逐条列出。 ↩ ↩2

  20. 出处:「Chapter 4: Shredding XML」第 338–342 段(text/08-ch04-chapter-4-shredding-xml.txt:339,搜「ANSI-SQL specification」)。原文:「The difference between the CONVERT and CAST functions is that the CONVERT function is not part of an ANSI-SQL specification, whereas CAST is」。 ↩

  21. 出处:「Chapter 4: Shredding XML」第 302–307 段(text/08-ch04-chapter-4-shredding-xml.txt:303,搜「XML data type methods: nodes()」)。原文:「SQL Server 2005 introduced the XML data type and XQuery language support via five XML data type methods: nodes(), value(), query(), exist(), and modify()」「the legacy stored procedures sp_xml_preparedocument and sp_xml_removedocument are obsolete…and are unused」。 ↩ ↩2

把 XML 文件装进数据库 — T-SQL 游标、SSIS 循环与 CLR 三条路

Previous Page

拆分 XML(下)— 列上批量拆、遗留存储、类型化列与嗅探

Next Page

On this page

拆分 XML(上)— OPENXML 的老 mechanics 与 XQuery 的接班1. 先看现象:一份制造指令,怎么变成一张表2. 顶层全景:两代工具的形状3. 核心原理3.1 OPENXML 的三段仪式:prepare、shred、remove3.2 命名空间:绕不过去的那道关3.3 行模式、@flags 与 WITH 里的导航:../ 和 @3.4 迁移到 XQuery:少掉的是仪式,多出来的是三个坑3.5 CONVERT 的 XML 样式参数:一个顺带的硬核知识点4. 作者的判断与证据5. 边界与局限6. 可带走的7. 原文地图Footnotes