教你使用SQL查询DB2 9中的XML数据

最后，如果有些 Gold 客户没有 email 地址，那么可能要编写一个查询从结果集中排除 null 值。为此可以修改之前的查询，添加另一个谓词到 WHERE 中，以测试是否缺少 email 信息。您已经熟悉了一个可以帮您实现这一点的 SQL/XML 函数 —— 那就是 XMLExists。清单 10 展示了如何重新编写之前的查询，以便过滤掉那些联系方式（存储为 XML 文档）中缺少 email 地址的 Gold 客户的行：

清单 10. 对于至少有一个 email 地址的客户，检索每个符合条件的客户的第一个 email 地址

select xmlquery('$c/Client/email[1]' passing contactinfo as "c") from clients 
where status = 'Gold'and xmlexists('$c/Client/email' passing contactinfo as "c")

现在我们考虑一个稍微不同的情景，假设您要检索多个 XML 元素值。XMLTable 可以从 XML 列中的数据生成标量输出，可以为程序员提供 XML 数据的 “关系” 视图，因此非常有用。与 XMLExists 和 XMLQuery 一样，XMLTable 函数使 DB2 在 XML 文档层次结构中定位到感兴趣的数据。然而，XMLTable 还包括一些子句，用于将目标 XML 数据映射到 SQL 数据类型的结果集列。

考虑以下查询（清单 11），该查询投影存储在 "items" 表中的关系数据和 XML 数据。（关于 "items" 表请查看 图 1）。评论 ID、客户 ID 和评语存储在 "comments" 列中的 XML 文档中。商品名称存储在一个 SQL VARCHAR 列中。

清单 11. 检索多个 XML 元素并将每个元素转换成传统的 SQL 数据类型

select t.Comment#, i.itemname, t.CustomerID, Message from items i, xmltable
('$c/Comments/Comment' passing i.comments as "c"columns Comment# integer path
 'CommentID',CustomerID integer path 'CustomerID',Message varchar(100) path 'Message') 
as t

第一行指定将包含在结果集中的列。查询中后面的几行表明，用引号括起来、并且以变量 "t" 为前缀的列是基于 XML 元素值的列。第二行调用 XMLTable 函数指定包含目标数据（"i.comments"）的 DB2 XML 列和在该列的 XML 文档中的路径，通过该路径可以定位感兴趣的元素（在根元素 "Comments" 的子元素 "Comment" 中）。第 3 到 5 行的 "columns" 子句标识出将被映射到第一行指定的 SQL 结果集中的输出列的特定 XML 元素。这个映射需要指定 XML 元素值将被转换成的数据类型。在这个例子中，所有 XML 数据被转换成传统的 SQL 数据类型。

图 4 展示了运行该查询得到的示例结果。可以看到，输出是一个简单的 SQL 结果集。注意，列名已经被变成大写形式 —— 这在 SQL 中是很常见的。

如果需要的话，还可以使用 XMLTable 创建包含 XML 文档的结果集。例如，以下语句产生类似于上述结果的结果集，不同的是 "Message" 数据被包含在一个 XML 列中，而不是包含在一个 SQL VARCHAR 列中。

清单 12. 检索多个 XML 元素并将它们转换成传统的 SQL 或 XML 数据类型

select t.Comment#, i.itemname, t.CustomerID, Message from items i, xmltable
('$c/Comments/Comment' passing i.comments as "c"columns Comment# integer path 
'CommentID',CustomerID integer path 'CustomerID',Message XML by ref path 'Message') as t

创建 XML 数据的关系视图

正如您可能想像到的那样，SQL/XML 函数可用于定义视图。如果要为 SQL 应用程序的程序员提供本地 XML 数据的关系模型，那么这种功能特别有用。

为 XML 列中的数据创建关系视图并不比投影 XML 元素值复杂多少。您只需编写一个 SQL/XML SELECT 语句，在语句中调用 XMLTable 函数，并以此作为视图定义的基础。下面 清单 13 中的例子基于 "items" 表的 XML 列和非 XML 列中的信息创建一个视图。（这类似于 清单 11 中的查询。）

清单 13. 基于 XMLTable 的输出创建视图

create view commentview(itemID, itemname, commentID, message, mustrespond) as 
select i.id, i.itemname, t.CommentID, t.Message, t.ResponseRequested from items i,
 xmltable('$c/Comments/Comment' passing i.comments as "c"columns CommentID integer 
path 'CommentID',Message varchar(100) path 'Message',ResponseRequested varchar(100)
 path 'ResponseRequested') as t;

虽然在 XML 列中的数据上创建关系视图很容易，但是用起来要小心。在对那样的视图发出查询时，DB2 不使用 XML 列索引。因此，如果以 ResponseRequested 列为索引，并发出一条将 "mustrespond" 列的结果限制为某个特定值的 SQL 查询，那么 DB2 将读取所有的 XML 文档，并搜索适当的 "ResponseRequested" 值。除非数据量不大，否则这样做会降低运行时性能。然而，如果在那些视图上运行的查询还包含有严格限制性的谓词，且参与索引的项中有传统的 SQL 类型的项（在这个例子中可以是 "i.id" 或 "i.itemname"），那么可以缓解潜在的运行时性能问题。DB2 使用关系索引将符合条件的行过滤到一个较小的量，然后在返回最终结果之前，将更多的 XML 查询谓词应用到这些临时的结果上。