跳转至

生成 XML — FOR XML 四种模式与一排指令

这一章讲什么: 数据库里的行和列,怎么变成一份 XML。答案是一个 2000 年就存在、 此后每个版本都在加码的 FOR XML 子句:它有四种模式、七个指令,组合起来 能从「每行一个 <row> 标签」一路做到「逐标签手工定制」。 读完你会得到的判断力: 拿到一个 XML 需求,十秒内说出该用哪种模式、加哪几条指令。

1. 先看现象:一个子句,四种模式

SQL Server 把「查询结果 → XML」的全部能力压在 SELECT … FOR XML … 这一句的尾巴上1。这个子句必须是整条 SELECT 的最后一个子句(有 ORDER BY 时排在它后面),它后面可以跟四种模式名2:

模式 一句话性格 什么时候用
RAW 每行变成一个叫 <row> 的元素,值全进属性 只要「快」,不在乎长相
AUTO 元素名自动取表名;多表连接时自动嵌套 单表直出、或层级恰好等于连接顺序
EXPLICIT 每个标签、每层父子关系都手写 需求变态到 PATH 表达不了(极少)
PATH 用 XPath 式别名描述层级,@ 表示属性 自定义结构的默认选择

模式之外还有一排指令(directive):ELEMENTS(改成元素中心)、ROOT(包一层根元素)、XSINIL(NULL 也出元素)、BINARY BASE64(二进制编码)、TYPE(返回真 XML 而非字符串)、XMLSCHEMA(内联校验卡)。它们才是决定输出长什么样的主力3。

2. 顶层全景:主线例子要走的四级台阶

本章主线例子沿用上一章的商品目录,目标是生成一份这样的 XML(原书配方 2-9 的目标结构):

<Products>                                  ← ROOT 指令包出来的根
  <Categories>
    <Category>Accessories</Category>        ← 元素中心,值在标签对里
    <Subcategory>Bike Racks</Subcategory>
    <Product Shelf="A" Bin="1" Quantity="8">  ← @ 开头的是属性
      <ProductName>Hitch Rack - 4-Bike</ProductName>
      <Color xsi:nil="true" />              ← 颜色为 NULL 也要占位(XSINIL)
    </Product>
  </Categories>
</Products>

(这份 XML 的骨架、标签名、Shelf/Bin/Quantity 这些属性名,全部来自原书 Listing 2-25 的目标结构;个别具体取值是演示用的。)

要走到它,主线例子要爬四级台阶:

  台阶一:RAW   —— 先让三行数据变成 XML(属性中心,<row> 元素)
  台阶二:AUTO + ELEMENTS + ROOT —— 自动起名、改成元素中心、包上根
  台阶三:认清两个「隐形坑」 —— NULL 默认消失;二进制默认给的是引用不是数据
  台阶四:PATH  —— 用 XPath 别名精确描述上面的目标结构(EXPLICIT 作为对照)

下面每节对应一级台阶。每节都回答四个问题:解决什么、怎么做、为什么有效、边界在哪。

3. 核心原理

3.1 RAW:最快的「行 → XML」,快在放弃一切控制

解决什么问题: 查询结果要先变成 XML,至于标签叫什么、值摆哪,都不讲究。

怎么做: 查询尾巴加 FOR XML RAW。结果里每行变成一个 <row> 元素,每个非空列值变成一个属性,属性名就是列名(或别名)4。拿主线例子的三行数据:

SELECT Category.Name AS CategoryName, Subcategory.Name AS SubcategoryName,
       Product.Name, Product.ProductNumber AS Number, Product.ListPrice AS Price
FROM Production.Product Product
     INNER JOIN Production.ProductSubcategory Subcategory …
     LEFT JOIN Production.ProductCategory Category …
WHERE Product.ListPrice > 0
ORDER BY CategoryName, SubcategoryName
FOR XML RAW;

输出(取自原书 Listing 2-2,行是 Components/Brakes 那组数据):

<row CategoryName="Components" SubcategoryName="Brakes" Name="Front Brakes" Number="FB-9873" Price="106.5000" />
<row CategoryName="Components" SubcategoryName="Brakes" Name="Rear Brakes" Number="RB-9231" Price="106.5000" />

为什么有效: 转换规则一一对应,没有任何猜测:一行一个元素、一列一个属性。

边界: 两条。第一,<row> 这个名字和数据毫无关系,直接发给客户「一般不合适也不符合业务」,改名的办法是 FOR XML RAW('Product')——括号里给行元素起业务名5。第二,RAW 不会嵌套——三行数据并排躺在顶层,表达不了「类别下面挂着子类别」这种层级6。

3.2 AUTO:让引擎替你起名和嵌套,代价是顺序由 FROM 决定

解决什么问题: 想让 XML 的元素名有点意义(用表名),多表查询时想要层级嵌套,但还不想手写结构。

怎么做: FOR XML AUTO。单表时,每行的元素名直接取查询里的表名——查 Production.Product,元素就叫 <Production.Product>;想改短,给表起别名,元素名跟着别名走(比如别名 Product,元素就是 <Product>)7。

多表连接时 AUTO 的性格才真正显出来:结果的层级按 FROM 子句里表的出现顺序嵌套。主线例子的三表查询,FROM 顺序是 Product → Subcategory → Category,于是 <Category> 是顶层,<Subcategory> 是它的孩子,<Product> 再下一层——完全自动8。

为什么有效: 书里解释了「AUTO」这个名字的来历:SQL 引擎分析你的查询结构,用查询里提供的名字自动生成元素和属性名9。

边界(这是 AUTO 的命门): 层级顺序被 FROM 钉死。想要别的嵌套顺序,就得重排 FROM——而 FROM 顺序一换,查询语义(连接方式)可能跟着变。所以 AUTO 只适合「我恰好就想要这个顺序」的场景。

3.3 ELEMENTS 与 ROOT:两条最常用的指令

ELEMENTS 解决什么问题: RAW 和 AUTO 默认都产出属性中心 XML(第 01 章说过,属性中心没法分层)。业务要求元素中心时,加 FOR XML AUTO, ELEMENTS——列值从属性搬进标签对里10。RAW、AUTO、PATH 三种模式都认这个指令,EXPLICIT 不认;指令和模式名之间用逗号分隔11。

ROOT 解决什么问题: 一份 XML 应该有唯一的顶层根元素(格式良好的要求之一),而 FOR XML 默认的输出是一串并列元素,没有根。FOR XML AUTO, ELEMENTS, ROOT 自动包一层 <root>;ROOT('Products') 可以指定根的名字12。

为什么有效: 这两条指令只是「重塑」引擎已经生成的结果,不改动数据本身,所以不会引入新的失败模式。

边界: 没有。这两个指令是全书使用频率最高的,记住语法即可。

3.4 XSINIL 与 ABSENT:NULL 的两种待遇——以及一个语法陷阱

解决什么问题: 数据库里某列是 NULL(没有值),生成的 XML 里该怎么办?

怎么做: ELEMENTS 指令下有两个选项,书里写得很明确13:

选项 NULL 列的下场 备注
ABSENT(默认) 这个元素直接不出现 不写也算选了它
XSINIL 元素保留,里面写一个 xsi:nil="true" 属性 明确的「占位符」语义

xsi:nil 的用处:下游程序能分清「这个字段没有值」和「这个字段压根不存在」——对数据交换场景,这个区别经常是要命的。

语法陷阱(原书特别强调): XSINIL/ABSENT 是 ELEMENTS 的选项,不是独立的指令,所以它们和 ELEMENTS 之间用空格隔开、不用逗号:FOR XML AUTO, ELEMENTS XSINIL, ROOT('Products')。写成 ELEMENTS, XSINIL 是错的14。

3.5 BINARY BASE64:二进制数据默认给你的是「收据」不是「货物」

解决什么问题: 列里是图片、文件这类二进制数据(varbinary 类型),想让它进 XML。

怎么做(以及踩坑顺序): 直接 FOR XML AUTO, ELEMENTS 查含二进制的列,报错:「FOR XML AUTO requires primary keys to create references for "LargePhoto." Select primary keys, or use BINARY BASE64…」——意思是:AUTO 模式没有主键就拒绝处理二进制15。把主键加进 SELECT,查询能跑了,但出来的不是图片数据,而是一个引用:dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@LargePhoto——相当于一张「货在 70 号库位」的收据16。真正想要数据,就加 BINARY BASE64 指令:二进制被编码成 Base64(一种把任意字节变成纯文本的编码)文本嵌进 XML17。

为什么有效: Base64 只含普通字符,不会和 XML 语法冲突,任何 XML 工具都能安全搬运。

四种模式对二进制的脾气,书里列了清单18:

模式 不加 BINARY BASE64 时
AUTO 有主键 → 给引用;无主键 → 报错
RAW / EXPLICIT 直接报错
PATH 不报错,直接返回数据(唯一的例外)

作者的明确建议: 只要结果集里有二进制,不管哪种模式都显式写上 BINARY BASE64——因为 SELECT 里一旦带了主键,AUTO 就会悄悄把数据换成引用,「你需要的是实际数据,不是数据的引用」19。

3.6 TYPE:防止「标签变成乱码」的那一道保险

解决什么问题: 用子查询生成嵌套 XML 时,出来的 XML 标签全变成了 < > 这种实体乱码。

怎么做(主线例子第三级台阶): 想生成「每个类别下面嵌套它的子类别」,自然想到相关子查询:

SELECT Category.Name AS CategoryName,
       (SELECT Subcategory.Name FROM … WHERE … FOR XML AUTO)   -- 子查询也生成 XML
FROM Production.ProductCategory Category
FOR XML AUTO, ROOT('Categories');

结果里的子查询输出是一堆 &lt; &gt;,根本不是标签20。原因是:FOR XML 默认返回的是 nvarchar(max) 字符串,不是 XML。子查询生成的「XML」被当成普通字符串塞进外层,字符串里的 < > 自然要按 01 章的实体规则转义21。修法:内外两层都加 TYPE 指令,强制返回真正的 XML 类型,不做实体化转义22:

… (SELECT Subcategory.Name FROM … FOR XML AUTO, TYPE) …

边界: TYPE 只在「XML 里再嵌 XML」(子查询、拼接、存进 XML 列)时才必需;单层输出用不用都行。但书里的态度是随手加上没坏处。

3.7 EXPLICIT:全手工模式的语法税法

解决什么问题: RAW/AUTO 给的长相都不满意,要对每个元素、每个属性的位置和形态做完全控制。

怎么做: EXPLICIT 模式查询的结构很特殊——多个 SELECT 块用 UNION ALL 串起来,每个块的头两列必须叫 Tag 和 Parent(两个整数),之后每一列的别名必须写成 [元素名!标签号!属性名!指令] 的四段式,段间用感叹号分隔23。

用主线例子的目标结构走一遍。五层结构 <Products><Categories><Category>…,对应的 Tag/Parent 配对是:

  第 1 块:Tag=1, Parent=0     → Categories 层(顶层,父亲为 0 表示没有父亲)
  第 2 块:Tag=2, Parent=1     → Subcategories 层(父亲 Tag 是 1)
  第 3 块:Tag=3, Parent=2     → Product 层(父亲 Tag 是 2)

书里把这种「上一块的 Tag 变成下一块的 Parent、同时自己加一」的接力叫 flip-flop(翻煎饼)机制24。每一层 SELECT 里的列别名举几个实例25:

别名 含义
[Categories!1!Category!ELEMENT] 第 1 层,值作为元素出现(ELEMENT 指令)
[Product!3!Shelf] 第 3 层,没写指令 → 默认作为属性
[Product!3!Color!ELEMENTXSINIL] 第 3 层,元素中心且 NULL 也保留

为什么有效: 引擎把 UNION ALL 的结果当作一张「通用表」(universal table)来读:Tag/Parent 两列描述树形结构,别名的四段式描述每个值落在哪个节点、当元素还是属性。

边界(六条税法,条条会报错):

  1. UNION ALL 串起来的每个 SELECT 块列数必须相同,用不上的位置填 NULL26。
  2. 同一个 Tag 号下,四段式的第一段(元素名)必须一致——就算数据来自不同的表,也得统一叫 Product27。
  3. ORDER BY 不能省:它决定父子节点在结果里的先后,排序列名必须和顶层 SELECT 的别名一致28。
  4. 别名里有 ! 等特殊字符,必须加方括号括起来。
  5. 指令集比 PATH 多:CDATA(内容不实体化)、HIDE(参与排序但不输出)、XMLTEXT(内容包进单个标签)、ID/IDREF/IDREFS(文档内链接)——PATH 全都给不了29。
  6. 查询长度爆炸:书里原话,EXPLICIT 的查询比其他模式「显著更长」30。

3.8 PATH:用别名画出 XML 的地图

解决什么问题: 想要 EXPLICIT 那种定制结构,但不想背六条税法。

怎么做: PATH 模式把层级直接写进列别名,用斜杠 / 一级级往下走,@ 开头表示属性。主线例子的目标结构,PATH 版查询长这样(取自原书 Listing 2-27,个别行有省略):

SELECT ProductCategory.Name AS "Category/CategoryName",
       Subcategory.Name     AS "Category/Subcategory/SubcategoryName",
       Inventory.Shelf      AS "Category/Subcategory/Product/ProductName/@Shelf",
       Inventory.Bin        AS "Category/Subcategory/Product/ProductName/@Bin",
       Inventory.Quantity   AS "Category/Subcategory/Product/ProductName/@Quantity",
       Product.Name         AS "Category/Subcategory/Product/ProductName",
       Product.Color        AS "Category/Subcategory/Product/Color"
FROM …(四张表连接)…
ORDER BY ProductCategory.Name, Subcategory.Name, Product.Name
FOR XML PATH('Categories'), ELEMENTS XSINIL, ROOT('Products');

读法:别名的路径就是 XML 里的位置。"Category/Subcategory/Product/ProductName/@Shelf" 的意思是:从 Categories 根下找 Category → Subcategory → Product → ProductName,把 Shelf 的值挂在 ProductName 的 Shelf 属性上31。同名路径的列自动合并到同一个节点——@Shelf、@Bin、@Quantity、ProductName 四条别名共享 .../ProductName 这段路径,于是它们落在同一个 <ProductName> 元素上(属性 + 文本内容)32。

为什么有效: 每个别名独立描述自己那一列的去向,引擎按路径归并——没有 Tag/Parent,没有 UNION ALL,没有「各块列数相同」的约束。

七条使用规则,书里列全了33:

  1. 别名路径要反映期望的层级(子写在父下面)。
  2. 每深一级,就在路径上加一段 /子元素名。
  3. 路径里出现 @ → 值渲染为属性;没有 @ → 渲染为元素。
  4. ORDER BY 在 PATH 里不决定层级(这点和 EXPLICIT 相反),可以不写;但按 XML 结构排序是好习惯。
  5. 建议总是 PATH('元素名') 给个根以下的第一层名字——默认的 <row>「不是好的 XML 设计」。
  6. PATH 里 XSINIL 是全局的:一开,所有元素都适用;EXPLICIT 的 ELEMENTXSINIL 只影响指定的那一个元素。
  7. 总是加 ROOT——「所有 XML 查询里都强烈建议用」。

边界: 指令比 EXPLICIT 少(没有 CDATA/HIDE/XMLTEXT 等);对极个别变态需求(比如要 IDREFS 文档内链接)只能回 EXPLICIT。作者在配方结尾给了裁决标准:先要分析你想要的 XML,再选模式——EXPLICIT 控制更强但复杂,PATH 好写但控制力稍弱34。

3.9 XPath 节点测试:往 XML 里塞注释、指令和纯文本

解决什么问题: 想往生成的 XML 里加注释()、处理指令(,给下游程序看的元信息)、或者把多个列的值拼成一个文本节点。

怎么做: PATH 模式支持一小撮 XPath 节点测试(node test)当别名的最后一段35:

节点测试 产出 例子
comment() <!--…--> 注释节点 N'Sales started ' + … AS "Category/comment()"
processing-instruction(name) <?name …?> 处理指令,括号里的目标名必填 … AS "processing-instruction(xml:file)"
text() 纯文本节点;多个 text() 列的值拼接进同一位置 Location.Name "text()", ', cost rate $', Location.CostRate "text()"
node() 通用节点,主要用来插入 XML 类型的值 子查询输出嵌入时配合 TYPE 用

边界(一个必须记住的坑): 节点测试名区分大小写且必须全小写。写成 Text() 会直接报错(错误号 6850:「Column name 'Text()' contains an invalid XML identifier…」)36。

3.10 主线例子走查:同一份定制 XML,EXPLICIT 与 PATH 各要多少行字

这一节把台阶四走实。 目标还是第 2 节那张 XML(Products → Categories → Category/Subcategories → Subcategory → Product),数据来自四张表的连接(类别、子类别、产品、库存)。原书在配方 2-8 和 2-9 里用同一目标各写了一遍,正好对照。

EXPLICIT 版(配方 2-8)的骨架,三个 SELECT 块用 UNION ALL 串联:

SELECT 1 AS Tag, 0 AS Parent,
       ProductCategory.Name AS [Categories!1!Category!ELEMENT],
       NULL AS [Subcategories!2!Subcategory!ELEMENT],
       NULL AS [Product!3!ProductName!ELEMENT],
       NULL AS [Product!3!Color!ELEMENTXSINIL],
       NULL AS [Product!3!Shelf],
       NULL AS [Product!3!Bin],
       NULL AS [Product!3!Quantity]
FROM Production.ProductCategory
UNION ALL
SELECT 2, 1, …          -- 第二层:Subcategories;不需要的列填 NULL
UNION ALL
SELECT 3, 2, …          -- 第三层:Product,含三个属性列
ORDER BY [Categories!1!Category!ELEMENT], [Subcategories!2!Subcategory!ELEMENT], …
FOR XML EXPLICIT, ROOT('Products');

(代码为讲解重构的骨架版,完整版见原书 Listing 2-23;三层的列数必须一致,所以每层都带着一排 NULL 占位。)

逐行读它的工作方式:Tag/Parent 两列把三个块缝成一棵树——第一层 Tag=1、Parent=0(没有父亲),第二层 Tag=2、Parent=1,第三层 Tag=3、Parent=2。别名决定每个值的身份:[Categories!1!Category!ELEMENT] 说「这是第 1 层的 Category 值,做成元素」;[Product!3!Shelf] 没写指令,默认做成属性;[Product!3!Color!ELEMENTXSINIL] 让颜色在 NULL 时也以 xsi:nil="true" 占位。ORDER BY 必须按「顶层别名、第二层别名、第三层别名」的父子顺序排,否则层级会散38。

PATH 版(配方 2-9),就是一个普通 SELECT,列名换成路径:

SELECT ProductCategory.Name AS "Category/CategoryName",
       Subcategory.Name     AS "Category/Subcategory/SubcategoryName",
       Inventory.Shelf      AS "Category/Subcategory/Product/ProductName/@Shelf",
       Inventory.Bin        AS "Category/Subcategory/Product/ProductName/@Bin",
       Inventory.Quantity   AS "Category/Subcategory/Product/ProductName/@Quantity",
       Product.Name         AS "Category/Subcategory/Product/ProductName",
       Product.Color        AS "Category/Subcategory/Product/Color"
FROM Production.Product Product
     INNER JOIN Production.ProductInventory Inventory ON …
     INNER JOIN Production.ProductSubcategory Subcategory ON …
     INNER JOIN Production.ProductCategory ON …
ORDER BY ProductCategory.Name, Subcategory.Name, Product.Name
FOR XML PATH('Categories'), ELEMENTS XSINIL, ROOT('Products');

两处值得停一下。第一,@Shelf、@Bin、@Quantity、ProductName 四条别名共享 Category/Subcategory/Product/ProductName 这段路径,引擎把四列合并进同一个 <ProductName> 元素:三个当属性,产品名当它的文本内容——合并规则就是「路径字符串相同,落在同一节点」。第二,ELEMENTS XSINIL 让整个文档所有元素都遵守「NULL 也占位」,对应 EXPLICIT 版里只对 Color 一列生效的 ELEMENTXSINIL——这是两种模式语义差异的一个缩影:EXPLICIT 的控制粒度到列,PATH 的指令粒度到整个结果39。

两份查询的对比账:

维度 EXPLICIT 版 PATH 版
SELECT 块 三个,UNION ALL 串联 一个
结构声明处 Tag/Parent 两列 + 四段式别名 列别名里的 / 路径
NULL 占位 逐列指令(ELEMENTXSINIL) 一个全局 XSINIL
排序的职责 ORDER BY 决定层级 ORDER BY 只影响显示顺序
能用的特殊指令 CDATA、HIDE、XMLTEXT、IDREFS 等 无
长度 「显著更长」40 接近普通查询

结论不是「PATH 处处更好」——而是除非需要 PATH 给不了的指令,定制结构都该从 PATH 写起;EXPLICIT 在今天的价值主要是读懂和维护遗留查询。

4. 作者的判断与证据

有演示直接支撑的: RAW/AUTO/PATH 的行为差异——每种模式都有同一组数据的输出对照,可以逐行比对4831;二进制与 TYPE 的坑——书里先给你看失败的查询和报错原文,再给修法1521,这是本章最有教学价值的部分。

作者明说的个人建议: 「只要结果集有二进制就总加 BINARY BASE64」19;「总是给 PATH 起元素名、总是加 ROOT」33;「选 EXPLICIT 还是 PATH,先分析目标 XML 再定」34。这些是经验判断,不是语法规定,但都是低成本高回报的习惯。

被原书轻描淡写、值得补一句的: EXPLICIT 模式在 2008 年被 PATH 取代之后,现实中的新增需求几乎不再需要它——书里仍用一整节教它,主要价值是让你读得懂遗留代码里的 EXPLICIT 查询(补充,不在书里,来自通用知识)。

5. 边界与局限

  • XMLSCHEMA 指令本章没展开: 它在第 01 章出现过(生成内联 Schema),本章指令表里列了但没给配方。
  • 性能几乎没谈: 哪种模式生成得更快、大数据量下有什么坑,本章一字未提;FOR XML 的输出在客户端怎么消费(流式读、写文件)是下一章的事。
  • 「2 MB 显示上限」是工具问题不是引擎问题: SSMS 默认只取回 2 MB 的 XML 结果,超了就抛「Unable to show XML」。改法:Tools → Options → Query Results → SQL Server → Results to Grid,把 Maximum Characters Retrieved 调成 5 MB 或 Unlimited;单次查询也可在 Query → Query Options 里临时改37。别把显示截断误认为数据截断。
  • EXPLICIT 的税法细节(通用表内部机制)比书里讲的更深: 原书没展开 universal table 的完整规范,遇到 EXPLICIT 报错时,官方文档的「使用 EXPLICIT 模式」一节是必要补充(补充,不在书里,来自通用知识)。

6. 可带走的

本章主线例子,一行走完: 三行商品目录 → FOR XML RAW 得 <row …/>(快、平、不能嵌套)→ FOR XML AUTO, ELEMENTS, ROOT('Products') 得自动嵌套的元素中心 XML(层级由 FROM 顺序定)→ 认坑:NULL 默认消失(要占位就 ELEMENTS XSINIL)、二进制默认给引用(要数据就 BINARY BASE64)、子查询输出是字符串(要真 XML 就 TYPE)→ FOR XML PATH('Categories') + XPath 别名 + @属性 精确复刻目标结构。

  1. FOR XML 是「模式 + 指令」两句话的事: 模式四选一(RAW/AUTO/EXPLICIT/PATH),指令按需求叠(ELEMENTS、ROOT、XSINIL、BINARY BASE64、TYPE)。
  2. 默认输出是属性中心字符串:要元素中心加 ELEMENTS(逗号分隔),要真 XML 类型加 TYPE。
  3. 层级要么交给 FROM 顺序(AUTO),要么自己画(PATH 别名)——没有第三条自动的路。
  4. NULL 默认整列消失;要区分「没值」和「没字段」,用 ELEMENTS XSINIL(注意空格不是逗号)。
  5. 二进制要数据不要引用:永远显式 BINARY BASE6419。
  6. 子查询生成 XML 必加 TYPE,否则标签变 &lt; 实体21。
  7. EXPLICIT 已死,PATH 当立:除非要 CDATA/HIDE/IDREFS 这类 PATH 没有的指令,自定义结构一律 PATH29。
  8. PATH 别名三规则: 斜杠分层、@ 转属性、同名路径自动合并;XSINIL 在 PATH 里是全局生效33。
  9. 注释/处理指令/文本拼接用节点测试,comment()、processing-instruction(name)、text()、node(),必须全小写3536。
  10. SSMS 显示 2 MB 上限可调——看到「Unable to show XML」先改设置,别怀疑数据37。

最后,一张「十秒选模式」的决策表(需求从上往下数,命中即停):

你的需求 选它 因为
只要 XML,长相无所谓 RAW(可加 ('名字')) 一行一个元素,最快
单表直出,元素名=表名 AUTO 自动起名
层级恰好等于 FROM 连接顺序 AUTO 自动嵌套
要元素中心 / 要 NULL 占位 / 要根元素 任意模式 + ELEMENTS / XSINIL / ROOT 指令与模式正交
要定制结构(元素属性混排、非常规层级) PATH 别名画地图,没有 EXPLICIT 的税法
要 CDATA、HIDE、XMLTEXT、文档内 ID 链接 EXPLICIT PATH 没有这些指令
结果集里有二进制 任意模式 + BINARY BASE64 不然 AUTO 给引用、RAW/EXPLICIT 报错18
XML 里还要嵌 XML(子查询) 内层加 TYPE 不然标签变实体21

7. 原文地图

主题 原书章 原文位置
FOR XML 子句位置与四种模式 Chapter 2: Building XML text/06-ch02-chapter-2-building-xml.txt:9(搜「clause in a SELECT statement」) · text/06-ch02-chapter-2-building-xml.txt:11(搜「following modes」)
RAW 模式与 <row> 默认输出 同上 text/06-ch02-chapter-2-building-xml.txt:94(搜「FOR XML clause to format query results」) · text/06-ch02-chapter-2-building-xml.txt:114(搜「converts each row」)
RAW 行标签改名 同上 text/06-ch02-chapter-2-building-xml.txt:136(搜「not suitable」) · text/06-ch02-chapter-2-building-xml.txt:141(搜「FOR XML RAW('ElementName')」)
AUTO 单表与多表嵌套 同上 text/06-ch02-chapter-2-building-xml.txt:177(搜「very similar in functionality」) · text/06-ch02-chapter-2-building-xml.txt:206(搜「does not support a row tag name」) · text/06-ch02-chapter-2-building-xml.txt:229(搜「nested multiple levels deep」)
ELEMENTS 指令 同上 text/06-ch02-chapter-2-building-xml.txt:298(搜「formats your XML result」)
ROOT 指令 同上 text/06-ch02-chapter-2-building-xml.txt:335(搜「wrapped in a single」)
XSINIL / ABSENT 与空格语法 同上 text/06-ch02-chapter-2-building-xml.txt:373(搜「XSINIL – This option」) · text/06-ch02-chapter-2-building-xml.txt:382(搜「by a space, not a comma」)
二进制的报错、引用与 BINARY BASE64 同上 text/06-ch02-chapter-2-building-xml.txt:400(搜「requires primary keys」) · text/06-ch02-chapter-2-building-xml.txt:441(搜「specific rules」) · text/06-ch02-chapter-2-building-xml.txt:460(搜「strongly recommend」)
TYPE 指令与实体化乱码 同上 text/06-ch02-chapter-2-building-xml.txt:489(搜「treated as a string data」) · text/06-ch02-chapter-2-building-xml.txt:519(搜「nvarchar(max)」)
EXPLICIT 的 Tag/Parent 与四段式别名 同上 text/06-ch02-chapter-2-building-xml.txt:640(搜「named Tag and Parent」) · text/06-ch02-chapter-2-building-xml.txt:668(搜「flip-flop」) · text/06-ch02-chapter-2-building-xml.txt:675(搜「AttributeName」)
EXPLICIT 独有指令表 同上 text/06-ch02-chapter-2-building-xml.txt:745(搜「ID, IDREF, IDREFS」)
PATH 的 XPath 别名与 @ 属性 同上 text/06-ch02-chapter-2-building-xml.txt:800(搜「XPath)-style column aliases」) · text/06-ch02-chapter-2-building-xml.txt:818(搜「present in the alias」)
PATH 七条规则与 EXPLICIT/PATH 裁决 同上 text/06-ch02-chapter-2-building-xml.txt:802(搜「follow these rules」) · text/06-ch02-chapter-2-building-xml.txt:852(搜「analyzing your prospect XML」)
XPath 节点测试与大小写坑 同上 text/06-ch02-chapter-2-building-xml.txt:888(搜「case-sensitive」) · text/06-ch02-chapter-2-building-xml.txt:901(搜「XPath node tests」)
本章小结与指令对照图 同上 text/06-ch02-chapter-2-building-xml.txt:948(搜「four FOR XML modes」)

  1. 出处:「Chapter 2: Building XML」第 9 段(text/06-ch02-chapter-2-building-xml.txt:9,搜「clause in a SELECT statement」)。原文:FOR XML「has to be the last clause in a SELECT statement and has to appear after an ORDER BY clause when the sorting takes place within a query」。 ↩

  2. 出处:「Chapter 2: Building XML」第 11–34 段(text/06-ch02-chapter-2-building-xml.txt:11,搜「following modes」)。四种模式与 RAW/AUTO/EXPLICIT/PATH 的官方性格描述都在这一段组里;PATH 是 2008 年引入的 EXPLICIT 简化替代(第 27 段,text/06-ch02-chapter-2-building-xml.txt:28,搜「simplified alternative」)。 ↩

  3. 出处:「Chapter 2: Building XML」第 36–55 段(text/06-ch02-chapter-2-building-xml.txt:37,搜「control formatting options」)。原文列了 ELEMENTS、BINARY BASE64、TYPE、ROOT、XSINIL、ABSENT、XMLSCHEMA 七个指令及各自定义。 ↩

  4. 出处:「Chapter 2: Building XML」第 113–118 段(text/06-ch02-chapter-2-building-xml.txt:114,搜「converts each row」)。原文:「RAW mode converts each row from a query result set into a simple, structured XML element」「all values are mapped to attributes with the same column names」「This XML structure is commonly referred to as attribute-centric XML」。 ↩↩

  5. 出处:「Chapter 2: Building XML」第 136–142 段(text/06-ch02-chapter-2-building-xml.txt:136,搜「not suitable」)。原文:「the default <row> element is generally not suitable or business applicable」;改名方式「FOR XML RAW('ElementName')」见第 140 段(text/06-ch02-chapter-2-building-xml.txt:140,搜「row tag name」)。 ↩

  6. 出处:「Chapter 2: Building XML」第 18 段(text/06-ch02-chapter-2-building-xml.txt:18,搜「does not support nested element structure」)。原文:「RAW mode does not support nested element structure」。 ↩

  7. 出处:「Chapter 2: Building XML」第 177–181 段(text/06-ch02-chapter-2-building-xml.txt:177,搜「very similar in functionality」)与第 206–208 段(text/06-ch02-chapter-2-building-xml.txt:206,搜「does not support a row tag name」)。AUTO 单表时「each row element will be <Production.Product>」;「AUTO mode, unlike RAW mode, does not support a row tag name option」——想改名只能给表起别名。 ↩

  8. 出处:「Chapter 2: Building XML」第 228–231 段(text/06-ch02-chapter-2-building-xml.txt:229,搜「nested multiple levels deep」)。原文:「The XML result is nested multiple levels deep, with each level of nested node named for the tables in the FROM clause in the order in which they are named」。 ↩↩

  9. 出处:「Chapter 2: Building XML」第 261–265 段(text/06-ch02-chapter-2-building-xml.txt:262,搜「The SQL query engine analyzes」)。原文:「The SQL query engine analyzes your query structure and builds a hierarchy using the names provided in the query」「this is why this mode is called "AUTO"」。 ↩

  10. 出处:「Chapter 2: Building XML」第 297–301 段(text/06-ch02-chapter-2-building-xml.txt:298,搜「formats your XML result」)。原文:「The ELEMENTS option formats your XML result with columns nested as sub-elements of each row element」。 ↩

  11. 出处:「Chapter 2: Building XML」第 300–303 段(text/06-ch02-chapter-2-building-xml.txt:300,搜「RAW, AUTO, and PATH modes」)。原文:ELEMENTS「applies to RAW, AUTO, and PATH modes only」,EXPLICIT 不支持;「must be separated from the RAW, AUTO, and PATH mode keywords by a comma」。 ↩

  12. 出处:「Chapter 2: Building XML」第 335–339 段(text/06-ch02-chapter-2-building-xml.txt:312,搜「top-level root element」)。原文:「By default, the ROOT directive generates a top-level element named <root>. However, the default name <root> can also be replaced with a user-defined value in parentheses」。 ↩

  13. 出处:「Chapter 2: Building XML」第 371–378 段(text/06-ch02-chapter-2-building-xml.txt:373,搜「XSINIL – This option」)。原文:XSINIL「forces the XML result to generate elements for NULL values」;ABSENT「leaves any elements that contain NULL values…out of your XML result. This is the default option」。 ↩

  14. 出处:「Chapter 2: Building XML」第 380–382 段(text/06-ch02-chapter-2-building-xml.txt:382,搜「by a space, not a comma」)。原文:「the XSINIL and ABSENT options are separated from their ELEMENTS directive by a space, not a comma」。 ↩

  15. 出处:「Chapter 2: Building XML」第 399–402 段(text/06-ch02-chapter-2-building-xml.txt:400,搜「requires primary keys」)。报错原文:「FOR XML AUTO requires primary keys to create references for "LargePhoto." Select primary keys, or use BINARY BASE64…」。 ↩↩

  16. 出处:「Chapter 2: Building XML」第 413–421 段(text/06-ch02-chapter-2-building-xml.txt:413,搜「a reference to the primary key row」)。输出形如 <LargePhoto>dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@LargePhoto</LargePhoto>。 ↩

  17. 出处:「Chapter 2: Building XML」第 425–428 段(text/06-ch02-chapter-2-building-xml.txt:427,搜「Base64-encoded format」)。原文:「apply the BINARY BASE64 directive…forces the XML result to include binary data in Base64-encoded format」。 ↩

  18. 出处:「Chapter 2: Building XML」第 441–447 段(text/06-ch02-chapter-2-building-xml.txt:441,搜「specific rules」)。四条规则:AUTO 带主键给引用;RAW 和 EXPLICIT 遇二进制报错;「Only PATH mode does not raise an error and returns the XML result with binary data when the BINARY BASE64 directive is not specified」。 ↩↩

  19. 出处:「Chapter 2: Building XML」第 460–463 段(text/06-ch02-chapter-2-building-xml.txt:460,搜「strongly recommend」)。原文:「I would strongly recommend including a BINARY BASE64 directive to all FOR XML modes when the binary data is part of the result set…you need to retrieve an actual datum, not the reference to the data」。 ↩↩↩

  20. 出处:「Chapter 2: Building XML」第 487–490 段(text/06-ch02-chapter-2-building-xml.txt:489,搜「treated as a string data」)。原文:「the XML being generated in the correlated subquery is treated as a string data type instead of proper XML data」。 ↩

  21. 出处:「Chapter 2: Building XML」第 518–522 段(text/06-ch02-chapter-2-building-xml.txt:519,搜「nvarchar(max)」)。原文:「By default, the FOR XML clause returns an nvarchar(max) data type result」;XML 转字符时「it properly entitizes certain special characters, like the "<" and ">" characters」。 ↩↩↩↩

  22. 出处:「Chapter 2: Building XML」第 523–524 段(text/06-ch02-chapter-2-building-xml.txt:523,搜「forces SQL to return」)。原文:「The TYPE directive forces SQL to return the XML results in proper XML format, without entitizing the contents. The TYPE directive can be used with all FOR XML modes」。 ↩

  23. 出处:「Chapter 2: Building XML」第 670–676 段(text/06-ch02-chapter-2-building-xml.txt:675,搜「AttributeName」)。原文:别名「must be formatted as [ElementName!TagNumber!AttributeName!Directive]」。 ↩

  24. 出处:「Chapter 2: Building XML」第 665–669 段(text/06-ch02-chapter-2-building-xml.txt:665,搜「Parent column starts」)。原文:「In the first SELECT query, the Parent column starts the hierarchy, with a value of 0…In the second SELECT query, the Tag value becomes the Parent and increments to the next number. This flip-flop mechanism is applied to each hierarchical level」。 ↩

  25. 出处:「Chapter 2: Building XML」第 678–712 段(text/06-ch02-chapter-2-building-xml.txt:682,搜「A generic identifier」)。四段式逐段解释与 [Product!3!Shelf](属性中心默认)等别名实例都在这一段组。 ↩

  26. 出处:「Chapter 2: Building XML」第 724–727 段(text/06-ch02-chapter-2-building-xml.txt:725,搜「UNION ALL operator rule」)。原文:「Each SELECT statement must have the same number of columns. When the column is not needed, it must be filled with NULL values」。 ↩

  27. 出处:「Chapter 2: Building XML」第 713–722 段(text/06-ch02-chapter-2-building-xml.txt:713,搜「must be the same within」)。原文:「The ElementName portion of the aliases must be the same within a given TagNumber, even if the values are retrieved from different tables」。 ↩

  28. 出处:「Chapter 2: Building XML」第 728–735 段(text/06-ch02-chapter-2-building-xml.txt:729,搜「finalizes the XML hierarchy」)。原文:「The ORDER BY clause finalizes the XML hierarchy…the sorting order needs to follow the parent-child sequence」。 ↩

  29. 出处:「Chapter 2: Building XML」第 742–759 段(text/06-ch02-chapter-2-building-xml.txt:745,搜「ID, IDREF, IDREFS」)与第 771–772 段(text/06-ch02-chapter-2-building-xml.txt:772,搜「does not have as many directive options」)。指令表含 ID/IDREF/IDREFS、CDATA、HIDE、ELEMENT、ELEMENTXSINIL、XML、XMLTEXT;PATH 的局限:「it does not have as many directive options as EXPLICIT mode」。 ↩↩

  30. 出处:「Chapter 2: Building XML」第 556–561 段(text/06-ch02-chapter-2-building-xml.txt:556,搜「most complex FOR XML modes」)。原文:「EXPLICIT mode is one of the most complex FOR XML modes to use」「the queries with EXPLICIT mode are significantly longer」。 ↩

  31. 出处:「Chapter 2: Building XML」第 796–801 段(text/06-ch02-chapter-2-building-xml.txt:800,搜「XPath)-style column aliases」)。原文:「the XML hierarchy is defined by XML Path Language (XPath)-style column aliases, where steps in the path are separated by forward slashes」。 ↩↩

  32. 出处:「Chapter 2: Building XML」第 777–785 段(text/06-ch02-chapter-2-building-xml.txt:777,搜「Category/CategoryName」)。Listing 2-27 里 @Shelf、@Bin、@Quantity 与 ProductName 共享 Category/Subcategory/Product/ProductName 路径前缀,引擎据此把它们合并进同一元素。 ↩

  33. 出处:「Chapter 2: Building XML」第 802–850 段(text/06-ch02-chapter-2-building-xml.txt:802,搜「follow these rules」)。七条规则逐条列出;「I would highly recommend utilizing the ROOT('ElementName') option in all XML queries」见第 848–850 段(text/06-ch02-chapter-2-building-xml.txt:849,搜「highly recommend」)。 ↩↩↩

  34. 出处:「Chapter 2: Building XML」第 852–856 段(text/06-ch02-chapter-2-building-xml.txt:852,搜「analyzing your prospect XML」)。原文:「I recommend analyzing your prospect XML, then making a choice of whether to use EXPLICIT or PATH mode」;「PATH mode is the opposite in the sense that it is easy to write the code; however, you have a bit less control」。 ↩↩

  35. 出处:「Chapter 2: Building XML」第 894–914 段(text/06-ch02-chapter-2-building-xml.txt:901,搜「XPath node tests」)。原文:「The node test is always located at the end of an XPath column alias」;表 2-2 列出 comment()、node()、processing-instruction(name)、text() 四种。 ↩↩

  36. 出处:「Chapter 2: Building XML」第 888–892 段(text/06-ch02-chapter-2-building-xml.txt:888,搜「case-sensitive」)。原文:「XPath node test names are case-sensitive…must be entered in lowercase」;Text() 的报错:「Msg 6850, Level 16, State 1 … Column name 'Text()' contains an invalid XML identifier」。 ↩↩

  37. 出处:「Chapter 2: Building XML」第 56–86 段(text/06-ch02-chapter-2-building-xml.txt:60,搜「2 MB」)。原文:大结果集会「exceed the default limit for XML data (2 MB) and throw a System.OutOfMemoryException with the message "Unable to show XML"」;改法在 Options 对话框的 Query Results → SQL Server → Result to Grid,可选 1 MB/2 MB/5 MB/Unlimited;单次查询可在 Query → Query Options 里临时改(第 83–86 段,text/06-ch02-chapter-2-building-xml.txt:85,搜「Query Options」)。 ↩↩

  38. 出处:「Chapter 2: Building XML」第 728–735 段(text/06-ch02-chapter-2-building-xml.txt:729,搜「finalizes the XML hierarchy」)。原文:「The ORDER BY clause finalizes the XML hierarchy. Therefore, the sorting order needs to follow the parent-child sequence. The column names in an ORDER BY clause must be the same as aliases in the top SELECT query」。 ↩

  39. 出处:「Chapter 2: Building XML」第 844–847 段(text/06-ch02-chapter-2-building-xml.txt:845,搜「automatically applied to all XML elements」)。原文:「When the XSINIL directive is implemented in the PATH mode, then this directive is automatically applied to all XML elements. However, in the EXPLICIT mode, the ELEMENTXSINIL directive only affects the element that the directive specifies」。 ↩

  40. 出处:「Chapter 2: Building XML」第 556–561 段(text/06-ch02-chapter-2-building-xml.txt:559,搜「significantly longer」)。原文:「When comparing EXPLICIT mode to other modes, such as RAW and AUTO, the queries with EXPLICIT mode are significantly longer」。 ↩