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

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

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

这一章讲什么: 上一篇(05)拆的是「一个 XML 变量」。本篇处理六个真实场景: 一整列 XML 逐行拆、存在老式 IMAGE 列里的 XML、挂了 Schema 的类型化 XML 特有的报错、 只取一棵子树、全库嗅探哪些列藏着 XML、以及用多级 CROSS APPLY 逐级下钻。 六个场景共享同一套零件,本篇把它们串成一条进阶路线。

1. 先看现象:一道查询拆全表

本章主线例子换成 Sales.Store 表的 Demographics 列。每行是一家商店,XML 是它的调查报告:年销售额(AnnualSales)、银行(BankName)、营业面积(SquareFeet)、员工数(NumberEmployees)……根元素是 <StoreSurvey>,下面一层平铺,没有更深嵌套——书里特意指出:列内 XML 保持一层浅结构是生产环境的常见设计,简单结构对性能友好1。

目标是:不把 XML 当黑箱逐行点开,而是一条查询把全表拆成行集——每家商店一行,调查字段各占一列。

2. 顶层全景:进阶路线的六站

  第一站 4-3  CROSS APPLY:把 nodes() 当表值函数,对表里每一行各拆一遍
  第二站 4-4  遗留存储:IMAGE 列 → VARBINARY(MAX) → XML,双命名空间,维护计划实战
  第三站 4-5  类型化 XML:Msg 9314 报错与 fn:string() 三_accessor
  第四站 4-6  query():不拆成行,只取一棵子树(还保持 XML 格式)
  第五站 4-7  嗅探:TRY_CONVERT 动态 SQL 扫全库,找出所有藏着 XML 的列
  第六站 4-8  多级 CROSS APPLY:一级级下钻,替代 ../ 导航

  图说:每站解决「列上批量拆」多走一步后撞上的新障碍。

3. 核心原理

3.1 CROSS APPLY:对每一行各拆一遍(第一站)

解决什么问题: nodes() 用在变量上,一次只拆一个文档;表里有几百行,就要拆几百次,而且不想写游标。

怎么做: SQL Server 2005 给了一对运算符:CROSS APPLY——对外层查询返回的每一行,调用一次右边的表值函数(table-valued function,返回一张表的函数),把各行的小结果集拼成一个大结果集。XML 的 nodes() 方法在 CROSS APPLY 眼里就是个表值函数2:

WITH XMLNAMESPACES(default 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey')
SELECT details.value('AnnualSales[1]', 'MONEY')        AS AnnualSales,
       details.value('AnnualRevenue[1]', 'MONEY')      AS AnnualRevenue,
       details.value('BankName[1]', 'VARCHAR(50)')     AS BankName,
       …(共 10 列)…
       details.value('NumberEmployees[1]', 'SMALLINT') AS NumberEmployees
FROM Sales.Store
CROSS APPLY Demographics.nodes('StoreSurvey') survey(details);

对照上一篇的变量版 FROM @x.nodes('StoreSurvey') survey(details),差别只有一处:数据源从「一个变量」换成「表 + CROSS APPLY + 列.nodes()」——但语义天差地别:变量版拆一份文档,APPLY 版对每行各拆一份、行集合并3。SELECT 列表的十个 value() 一个不用改。

OUTER APPLY: 相当于 LEFT OUTER JOIN——外层行即使 XML 为空也保留,拆不出东西就补 NULL。本例 Demographics 没有 NULL 行,两种 APPLY 结果相同4。

为什么有效: APPLY 把「逐行处理」从游标的手艺变成集合运算,一条查询、一个执行计划,数据库优化器(决定 SQL 怎么执行的部件)随便发挥。

边界(大小写坑,新手上书第一课): 元素和属性在 XQuery 里严格区分大小写。把 AnnualSales 写成 annualsales,类型化 XML 直接报错(Msg 2263,错误信息里会指出它期望的元素全名);无类型 XML 不报错、默默给 NULL——两种死法都不好受5。书里的提醒很扎心:T-SQL 纵容 isnull() 乱写大小写,把人惯坏了;而 XML 方法名、元素名、属性名,一律照原文大小写6。

3.2 遗留存储:XML 缩在 IMAGE 列里(第二站)

解决什么问题: 老库的 XML 不一定存在 XML 列里——VARCHAR、NVARCHAR、VARBINARY、IMAGE、TEXT、NTEXT 都可能7。 nodes() 只认 XML 类型,得先转正。

怎么做,两步 CAST(本章最妙的一行 SQL): 系统表 msdb.dbo.sysssispackages 的 packagedata 列是 IMAGE 类型,存的全部是 SSIS 包——SSIS 包本身就是 XML8。但 IMAGE 不能直接转 XML,必须先转 VARBINARY(MAX) 当中转:

CAST(CAST(packagedata AS varbinary(MAX)) AS XML)

实战场景(这是主线例子的高光): 你有几百台 SQL Server 实例,想知道哪些数据库的备份跑过维护计划(Maintenance Plan)、备份到哪个目录。逐台登录打开维护计划界面是不可能的;正确姿势是直接拆 msdb 里存维护计划的包 XML,一行查询出清单9。

配套的三个技巧:

  1. 双命名空间齐声明:维护计划的包 XML 里有两个命名空间——www.microsoft.com/SqlServer/Dts(DTS,顶层元素)和 www.microsoft.com/sqlserver/dts/tasks/sqltask(SQLTask,任务元素),WITH XMLNAMESPACES 里两个都要10。
  2. // 捷径路径:包 XML 很大,而 nodes() 的行模式不必从根一级级写全——'//DTS:ObjectData/SQLTask:SqlTaskData/SQLTask:SelectedDatabases' 开头的 // 表示「不管前面有几层,匹配右端这个结构」。书里把它类比成 T-SQL 的 LIKE '%text%'11。
  3. packagetype=6 过滤:sysssispackages 的 packagetype 列标记包的种类,6 = Maintenance Plan Designer or Wizard(维护计划设计器或向导)——只看 5(SSIS 设计器)或 6,按场景过滤12。

取值时,行模式停在 SelectedDatabases 元素上;要拿它的父级元素 SqlTaskData 上的 BackupDestinationAutoFolderPath 属性,用 ../ 上一级——和上一篇的 ../@ 导航一模一样13。

3.3 类型化 XML:Msg 9314 与 fn:string() 三 accessor(第三站)

解决什么问题: 上一篇的拆分套路,原样搬到类型化(typed)XML 列上,可能直接报错:

Msg 9314 … Cannot implicitly atomize or apply 'fn:data()' to complex content elements

原因:类型化 XML 的元素带着 Schema 给的类型(比如 step 是 StepType——一个有结构的复杂类型,不是纯文本)。value() 想把复杂类型当单值取,引擎拒绝14。

怎么做: 给路径包一层 fn:string()——XQuery 的数据存取函数,把元素或属性的值提取成字符串,绕过类型的复杂性:

instruct.value('fn:string(.)', 'varchar(2000)')            AS StepInstruction,
instruct.value('fn:string(../@LocationID)', 'int')          AS LaborStation,
instruct.value('df:material[1]', 'varchar(100)')            AS Material

(注意 df:material 那种 Schema 里就是简单字符串类型的元素,不需要 fn:string;报错的只有复杂类型那几个。)15

三个 accessor 的分工,书里用实验讲清了16:

函数干什么什么时候翻车
text()返回元素下的单个文本节点内容不止一段文本时只回一部分(部分值)
fn:data()提取标量(带类型的值)撞上复杂类型 → 报错
fn:string()提取字符串值,要求单例最通用;本场景的答案

边界: 无类型 XML 列上三个函数差别不大;类型化列上选错就是报错或截断——先想清楚目标元素在 Schema 里是简单类型还是复杂类型(补充判断,不在书里,依据三函数定义推得)。

3.4 query():不拆行,只取子树(第四站)

解决什么问题: 有时不想要行和列,就要 XML 的某一段,还保持 XML 格式——比如从执行计划里只抽出 <Statements> 部分。

怎么做: query() 方法,参数是要取的子树路径,返回 XML 类型17。书里两个有代表性的例子:

-- 从执行计划 DMV 抽语句段(单一命名空间 → XQuery 内联 declare default)
query_plan.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //Batch/Statements')

-- 给子树套一个自定义根元素:花括号是 XQuery 的「值拼接」语法
.query('<Root>{//DTS:ObjectData//SQLTask:SqlTaskData/SQLTask:SelectedDatabases}</Root>')

命名空间声明有两套写法,任选:WITH XMLNAMESPACES(T-SQL 侧),或 XQuery 内部的 declare namespace/default element namespace(查询内部)18。{…} 花括号语法能在构造 XML 时把路径结果嵌进去,所以可以给抽出的子树自己套一个根元素——这是 query() 独有的轻量构造能力19。

边界: query() 不做任何拆分——行集化还是 value() 的活儿;它只负责「剪枝搬运」。

3.5 嗅探:全库哪些列藏着 XML(第五站)

解决什么问题: 接手一个陌生的库,XML 可能藏在任何文本列里(第 01 章说过:不一定用 XML 类型存)。想知道哪儿有 XML,总不能逐列点开看。

怎么做,动态 SQL 嗅探器(原书 Listing 4-30 的思路,代码较长):从系统视图 sys.columns、sys.types、sys.objects、sys.schemas 捞出所有可能存 XML 的列;对每列生成一条探测 SQL,用游标逐个执行20。核心机关是 TRY_CONVERT(XML, 列):转换失败返回 NULL,不报错(CAST/CONVERT 会抛 Msg 9420)。转成功的,顺手用 fn:local-name(.)[1] 读出根元素名作为证据——值和根名一起进结果表,最后汇总输出「哪张表、哪个列、根元素叫什么」21。

两个过滤细节:CHAR/VARCHAR/NVARCHAR/VARBINARY 列只查 max_length = -1(即 MAX)的——XML 天生是长文本,短列不用看;IMAGE 类型走前面那套双重 CAST 探测22。TRY_CONVERT 是 2012 年才有的函数,这是本方案的版本底线23。

//* 与 local-name(..) 两个附赠技巧: 把 nodes() 的行模式设成 //*(所有层级的所有元素),配合 local-name(.)(当前元素名)和 local-name(..)(父元素名),可以列出一棵 XML 的全部元素清单及父子关系——拆陌生文档前的标准侦察动作24。

3.6 多级 CROSS APPLY:逐级下钻(第六站)

解决什么问题: 上一篇用 ../ 在 value() 里上下导航;层级一深,路径里全是 ../../..,可读性崩溃。

怎么做: 把层级摊进 FROM 子句,一级一个 CROSS APPLY,下一级作用在上一级的结果别名上:

FROM Production.ProductModel
     CROSS APPLY Instructions.nodes('df:root/df:Location') prod(instruct)  -- 第一级:工位
     CROSS APPLY instruct.nodes('df:step') ins(step)                       -- 第二级:工序

第一级把每个 Location 拆成一行(别名 instruct 指向 Location 节点);第二级再对每行把它的 step 孩子们拆出来(别名 step)。SELECT 里 instruct.value('@LocationID','int') 取父级属性、step.value('df:material[1]',…) 取子级元素——每级的别名就是该级的「当前节点」,不用 ../ 来回爬25。

关键结论(书里明写): 多级 CROSS APPLY 和 4-5 的 fn:string + ../ 方案,产出的结果集完全相同,差别只在 SELECT/FROM 的写法26。所以这是风格之争,不是对错之争:层级浅用 ../,层级深或要逐级过滤时用多级 APPLY。

4. 作者的判断与证据

有对照实验支撑的: 三个 accessor 函数的差别(text() 回部分值、fn:data() 报错、fn:string() 正确)有同一份 XML、三种写法、结果截图的对照16;多级 CROSS APPLY 与 ../ 方案「absolutely the same result set」是书里直接断言、可跑码验证的26。

作者的经验之谈: 「一层浅 XML 是生产常见设计,出于性能考虑」1;「生产环境别用 T(C) 这种没信息量的别名」(上一篇)18;「单靠 XML 数据类型找 XML 不是好策略,得嗅探」27——这些都是 DB A 老手的现场判断,书里以第一人称给出。

本章最值得留意的元信息: 章末小结列了 SQL Server 内建进程里用 XML 的地方:执行计划、Extended Events、DDL 触发器、SSIS/SSRS 的底层代码28——这一句是第 11 章(整章讲内建 XML 用法)的预告,也是「为什么要学拆分」的最硬理由:你不是在学一个冷门功能,是在学读数据库自己的「体检报告」的语言。

5. 边界与局限

  • 嗅探器跑大库很慢:逐列 TRY_CONVERT 是全表扫描级的操作,生产库上跑要挑维护窗口(补充判断,不在书里)。
  • OUTER APPLY 的 NULL 语义只在列可能为空时才有意义;书中样例列无 NULL,两种 APPLY 没区别——读者容易误以为 APPLY 无差别,注意前提4。
  • // 捷径的代价:从根搜索比精确路径慢,大文档上更明显;原书没量化(补充判断,不在书里)。
  • DMV 示例(sys.dm_exec_query_stats)的口径随版本变化,query() 的语法不变。

6. 可带走的

本章主线例子,一行走完: StoreSurvey 列(CROSS APPLY 逐行拆出 10 列调查报告)→ 换到 msdb 的 IMAGE 列(双重 CAST 转正,双命名空间,// 捷径,packagetype=6 滤出维护计划,../ 取备份目录)→ 类型化列报错 9314(fn:string() 解围)→ 只要子树用 query()(还能套自定义根)→ 不知道 XML 在哪用 TRY_CONVERT 嗅探(根元素名作证据)→ 层级深了用多级 CROSS APPLY 逐级下钻。

  1. 列.nodes() 必须配 CROSS APPLY 才能进 FROM;对每行各拆一份、结果合并,SELECT 与变量版完全一样3。
  2. OUTER APPLY ≈ LEFT JOIN:XML 为空的行也保留4。
  3. XQuery 里大小写全敏感:元素名错 = 类型化报错 / 无类型静默 NULL;方法名大写直接报错56。
  4. IMAGE 转 XML 要双重 CAST:CAST(CAST(列 AS varbinary(MAX)) AS XML),IMAGE 不能直接转8。
  5. // 路径 = 「不管几层,匹配右端」,类比 LIKE '%…%';超大文档慎用11。
  6. 类型化 XML 拆出 Msg 9314,就给复杂类型包 fn:string();三 accessor:text() 可能截断、fn:data() 撞复杂类型报错、fn:string() 最稳1416。
  7. query() 取子树保 XML 格式,花括号语法可套自定义根元素19。
  8. 嗅探 XML 用 TRY_CONVERT(失败返 NULL 不炸)+ 系统视图 + fn:local-name 报根名21。
  9. local-name(..) 报父元素名,//* 列全元素——拆陌生文档前的侦察两件套24。
  10. 多级 CROSS APPLY = 层级摊进 FROM,每级别名即当前节点;与 ../ 导航结果相同,层级深时胜2526。

7. 原文地图

主题原书章原文位置
StoreSurvey 与浅层结构Chapter 4: Shredding XMLtext/08-ch04-chapter-4-shredding-xml.txt:486(搜「Demographics」) · text/08-ch04-chapter-4-shredding-xml.txt:521(搜「one-level deep」)
CROSS APPLY / OUTER APPLY 定义同上text/08-ch04-chapter-4-shredding-xml.txt:528(搜「CROSS APPLY – Allows」)
变量版与列版的对照同上text/08-ch04-chapter-4-shredding-xml.txt:554(搜「single document」)
大小写敏感与两种死法同上text/08-ch04-chapter-4-shredding-xml.txt:575(搜「case sensitive」)
遗留存储与双重 CAST同上text/08-ch04-chapter-4-shredding-xml.txt:598(搜「Legacy XML Storage」) · text/08-ch04-chapter-4-shredding-xml.txt:666(搜「eligible to be converted」)
维护计划实战场景同上text/08-ch04-chapter-4-shredding-xml.txt:625(搜「hundred SQL Server instances」)
双命名空间与 // 捷径同上text/08-ch04-chapter-4-shredding-xml.txt:655(搜「two namespaces」) · text/08-ch04-chapter-4-shredding-xml.txt:686(搜「shortcut path」)
packagetype=6同上text/08-ch04-chapter-4-shredding-xml.txt:668(搜「five possible values」)
Msg 9314 与 fn:string()同上text/08-ch04-chapter-4-shredding-xml.txt:737(搜「Msg 9314」) · text/08-ch04-chapter-4-shredding-xml.txt:745(搜「fn:string()」)
三 accessor 函数同上text/08-ch04-chapter-4-shredding-xml.txt:771(搜「three data accessor functions」)
query() 取子树与自定义根同上text/08-ch04-chapter-4-shredding-xml.txt:831(搜「specific part」) · text/08-ch04-chapter-4-shredding-xml.txt:917(搜「user-defined root element」)
TRY_CONVERT 嗅探同上text/08-ch04-chapter-4-shredding-xml.txt:942(搜「sniffs」) · text/08-ch04-chapter-4-shredding-xml.txt:1025(搜「TRY_CONVERT() behavior」)
local-name 与 //*同上text/08-ch04-chapter-4-shredding-xml.txt:1084(搜「fn:local-name()」) · text/08-ch04-chapter-4-shredding-xml.txt:1091(搜「descendant-or-self」)
多级 CROSS APPLY同上text/08-ch04-chapter-4-shredding-xml.txt:1148(搜「applying one CROSS APPLY」)
内建进程用 XML 的清单同上text/08-ch04-chapter-4-shredding-xml.txt:1171(搜「built-in SQL Server processes」)

Footnotes

  1. 出处:「Chapter 4: Shredding XML」第 486–491 段(text/08-ch04-chapter-4-shredding-xml.txt:486,搜「Demographics」)与第 519–526 段(text/08-ch04-chapter-4-shredding-xml.txt:521,搜「one-level deep」)。原文:「This is a one-level deep XML structure. Such a structure is common in production environments because database designers prefer to keep a simple XML structure within a column when possible for performance reasons」。 ↩ ↩2

  2. 出处:「Chapter 4: Shredding XML」第 527–535 段(text/08-ch04-chapter-4-shredding-xml.txt:528,搜「CROSS APPLY – Allows」)。原文:「CROSS APPLY – Allows a table-valued function to be invoked on each row returned by an outer-table expression of a query. The XML nodes() method is treated as a table-valued function in terms of CROSS APPLY」「OUTER APPLY – Equivalent to LEFT OUTER JOIN」。 ↩

  3. 出处:「Chapter 4: Shredding XML」第 552–566 段(text/08-ch04-chapter-4-shredding-xml.txt:554,搜「single document」)。原文:「the nodes() method applied to a variable returns a rowset that represents the result of shredding a single document. This is as opposed to CROSS APPLY against a table, which shreds the XML from each row of the table and generates a single rowset」「The SELECT clause is identical for both」。 ↩ ↩2

  4. 出处:「Chapter 4: Shredding XML」第 536–538 段(text/08-ch04-chapter-4-shredding-xml.txt:537,搜「OUTER APPLY operator」)。原文:「the OUTER APPLY operator returns the same result set, because the Demographics column does not have any rows containing NULL values」。 ↩ ↩2 ↩3

  5. 出处:「Chapter 4: Shredding XML」第 575–585 段(text/08-ch04-chapter-4-shredding-xml.txt:575,搜「case sensitive」)。原文:「elements and attributes are case sensitive」「for XML type columns, the parser will throw an error」,例:小写 annualsales 收到 Msg 2263;「the incorrectly specified item will be NULL」。 ↩ ↩2

  6. 出处:「Chapter 4: Shredding XML」第 586–596 段(text/08-ch04-chapter-4-shredding-xml.txt:586,搜「common mistake」)。原文:「SQL Server T-SQL does not raise any errors when we type the function ISNULL(), for instance, in lower, upper, or mixed cases」「we must follow the case rules」。 ↩ ↩2

  7. 出处:「Chapter 4: Shredding XML」第 604–614 段(text/08-ch04-chapter-4-shredding-xml.txt:598,搜「Legacy XML Storage」)。原文:「in legacy databases, XML documents could be stored in columns of VARCHAR, NVARCHAR, VARBINARY, IMAGE, TEXT, and NTEXT data types. In this case, the column has to be converted to the XML data type」。 ↩

  8. 出处:「Chapter 4: Shredding XML」第 616–618 段(text/08-ch04-chapter-4-shredding-xml.txt:617,搜「sysssispackages」)。原文:「SQL Server continues to use the IMAGE data type to store SSIS packages in the msdb.dbo.sysssispackages table…All SSIS packages are XML documents」;双重转换「CAST(CAST(packagedata AS varbinary(MAX)) AS XML)」见第 665–667 段(text/08-ch04-chapter-4-shredding-xml.txt:666,搜「eligible to be converted」)。 ↩ ↩2

  9. 出处:「Chapter 4: Shredding XML」第 622–627 段(text/08-ch04-chapter-4-shredding-xml.txt:625,搜「hundred SQL Server instances」)。原文:「You also need to inspect several hundred SQL Server instances. Instead of logging in to each instance and opening each individual Maintenance Plan, you can shred the packagedata column from the sysssispackages table…this is a much more efficient process」。 ↩

  10. 出处:「Chapter 4: Shredding XML」第 655–660 段(text/08-ch04-chapter-4-shredding-xml.txt:655,搜「two namespaces」)。原文:「These SQL Maintenance Job SSIS packages contain two namespaces, so the XMLNAMESPACES declaration has to include them both」:'www.microsoft.com/SqlServer/Dts' 与 'www.microsoft.com/sqlserver/dts/tasks/sqltask'。 ↩

  11. 出处:「Chapter 4: Shredding XML」第 685–689 段(text/08-ch04-chapter-4-shredding-xml.txt:686,搜「shortcut path」)。原文:「The SSIS XML is large; therefore, "//" provides a shortcut path to the source element and matches an expression pattern within the XML document」「You can also use a leading wildcard "%" in a WHERE clause」。 ↩ ↩2

  12. 出处:「Chapter 4: Shredding XML」第 668–680 段(text/08-ch04-chapter-4-shredding-xml.txt:668,搜「five possible values」)。五个值:0=Default、1=Import and Export Wizard、3=Replication、5=SSIS Designer、6=「Maintenance Plan Designer or Wizard」;「we are focusing on Maintenance Plans; therefore, we can filter the result to packagetype = 6」。 ↩

  13. 出处:「Chapter 4: Shredding XML」第 704–708 段(text/08-ch04-chapter-4-shredding-xml.txt:707,搜「step up one XML level」)。原文:「to access the SQLTask:BackupDestinationAutoFolderPath attribute, we need to step up one XML level to SQLTask:SqlTaskData element. The step operator "../" completes the process」。 ↩

  14. 出处:「Chapter 4: Shredding XML」第 737–747 段(text/08-ch04-chapter-4-shredding-xml.txt:737,搜「Msg 9314」)。报错原文:「Cannot implicitly atomize or apply 'fn:data()' to complex content elements, found type…StepType」;修法:「we have to introduce the XQuery data accessor function fn:string() to prevent the error」。 ↩ ↩2

  15. 出处:「Chapter 4: Shredding XML」第 749–765 段(text/08-ch04-chapter-4-shredding-xml.txt:749,搜「fn:string() function」)。Listing 4-21 的写法:instruct.value('fn:string(.)', 'varchar(2000)')、instruct.value('fn:string(../@LocationID)', 'int'),而 df:material[1] 不带 fn:string。 ↩

  16. 出处:「Chapter 4: Shredding XML」第 771–808 段(text/08-ch04-chapter-4-shredding-xml.txt:771,搜「three data accessor functions」)。原文:「fn:string() – extracts the string values」「fn:data() – extracts scalar (typed) values」「text() – returns a single value」;实验结论:「text() function returns a single value""fn:data() returns an error, and the text() function returns a partial value」。 ↩ ↩2 ↩3

  17. 出处:「Chapter 4: Shredding XML」第 825–851 段(text/08-ch04-chapter-4-shredding-xml.txt:831,搜「specific part」)。原文:「The query() method allows you to retrieve a specific part of an XML instance」「The query() method returns an instance of the XML data type」。 ↩

  18. 出处:「Chapter 4: Shredding XML」第 875–907 段(text/08-ch04-chapter-4-shredding-xml.txt:882,搜「declare default element namespace」)。执行计划例子用 XQuery 内联 declare default element namespace;「You can pick your own preference on which syntax to use」。 ↩ ↩2

  19. 出处:「Chapter 4: Shredding XML」第 916–930 段(text/08-ch04-chapter-4-shredding-xml.txt:917,搜「user-defined root element」)。原文:「the subset of the XML instance is returned with a user-defined root element…The syntax for this is query('<Root>{/XMLPath/}</Root>')」。 ↩ ↩2

  20. 出处:「Chapter 4: Shredding XML」第 938–1006 段(text/08-ch04-chapter-4-shredding-xml.txt:942,搜「sniffs」)。原文:「Simply relying on the XML data type is not a good strategy」「I developed a SQL script that dynamically "sniffs" the XML document across all columns within a database」;「The FROM clause obtains from the system tables (sys.columns, sys.types, sys.objects, and sys.schemas)」。 ↩

  21. 出处:「Chapter 4: Shredding XML」第 1023–1035 段(text/08-ch04-chapter-4-shredding-xml.txt:1025,搜「TRY_CONVERT() behavior」)。原文:「We are taking advantage of the TRY_CONVERT() behavior: when a data type fails to convert to the specified type, then the function returns NULL」「CAST() and CONVERT() functions return an error (Msg 9420…)」;根元素名:c.value('fn:local-name(.)[1]', 'VARCHAR(200)') AS TopNodeName。 ↩ ↩2

  22. 出处:「Chapter 4: Shredding XML」第 1016–1022 段(text/08-ch04-chapter-4-shredding-xml.txt:1020,搜「length of -1」)。原文:「the data types VARCHAR, NVARCHAR, and VARBINARY are expected to have a length of -1, that is, MAX」;IMAGE 的双路探测见第 1031–1035 段。 ↩

  23. 出处:「Chapter 4: Shredding XML」第 1008–1009 段(text/08-ch04-chapter-4-shredding-xml.txt:1009,搜「available on SQL Server 2012 and later」)——注意:原文此处在 text/08-ch04-chapter-4-shredding-xml.txt:1009(搜「available on SQL Server 2012 and later」):「the TRY_CONVERT() function, which is only available on SQL Server 2012 and later」。 ↩

  24. 出处:「Chapter 4: Shredding XML」第 1090–1098 段(text/08-ch04-chapter-4-shredding-xml.txt:1091,搜「descendant-or-self」)。原文:「the node() method is set with "//*" (a shortcut for the descendant-or-self axis), which means to look and visit every element in the XML data」「details.value('local-name(..)[1]', 'VARCHAR(100)')…returns the element that resides one level up, that is, parent」。 ↩ ↩2

  25. 出处:「Chapter 4: Shredding XML」第 1147–1163 段(text/08-ch04-chapter-4-shredding-xml.txt:1148,搜「applying one CROSS APPLY」)。原文:「The key to using multiple CROSS APPLY operators is applying one CROSS APPLY to the results of the second CROSS APPLY」「The second CROSS APPLY takes the results of the first CROSS APPLY via its instruct column alias」。 ↩ ↩2

  26. 出处:「Chapter 4: Shredding XML」第 1165–1167 段(text/08-ch04-chapter-4-shredding-xml.txt:1167,搜「absolutely the same result set」)。原文:「The solutions in Recipes 4-5 and 4-8 produce absolutely the same result set, but the syntax in the SELECT and FROM clauses is different」。 ↩ ↩2 ↩3

  27. 出处:「Chapter 4: Shredding XML」第 938–943 段(text/08-ch04-chapter-4-shredding-xml.txt:939,搜「client side」)。原文:「Simply relying on the XML data type is not a good strategy」。 ↩

  28. 出处:「Chapter 4: Shredding XML」第 1170–1182 段(text/08-ch04-chapter-4-shredding-xml.txt:1171,搜「built-in SQL Server processes」)。清单:「Execution Plans」「Extended Events」「DDL triggers」「SSIS and SSRS code behind」;「The XQuery language simplifies SQL Server and the tasks performed by DBAs and developers」。 ↩

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

Previous Page

修改 XML — modify() 的三板斧:insert、replace value of、delete

Next Page

On this page

拆分 XML(下)— 列上批量拆、遗留存储、类型化列与嗅探1. 先看现象:一道查询拆全表2. 顶层全景:进阶路线的六站3. 核心原理3.1 CROSS APPLY:对每一行各拆一遍(第一站)3.2 遗留存储:XML 缩在 IMAGE 列里(第二站)3.3 类型化 XML:Msg 9314 与 fn:string() 三 accessor(第三站)3.4 query():不拆行,只取子树(第四站)3.5 嗅探:全库哪些列藏着 XML(第五站)3.6 多级 CROSS APPLY:逐级下钻(第六站)4. 作者的判断与证据5. 边界与局限6. 可带走的7. 原文地图Footnotes