级别： 初级 developerWorks 中国网站编辑团队, 编辑, IBM
2009 年 4 月 16 日 本系列文章为使用 DB2 pureXML™ 来有效的解决商业问题和在企业应用程序中高性能的管理 XML 数据提供了原理和指南。在样例中举例说明了基于真实世界金融应用场景的最佳实践，并示范了如果执行这个指南。这个例子可以很容易被应用于其它类型的 XML 应用程序。系列第 1 部分指出了为什么要使用 XML 并概述了 pureXML ，第 2 部分描述如何向一个 DB2 数据库中有效添加 XML 数据技术。本部分主要讲如何高效并有效率的查询 XML 数据。
在这一章节，我们将讨论关于什么时候使用 SQL/XML 或 XQuery 来编写查询，通过例子来使用我们的样本数据库。我们将讨论从趋势到棘手的问题，包括使用 // 和 *, 在 SQL/XML 查询中有效的限制行，XMLEXISTS 谓词的一般错误，以及处理 XML 属性数据。其它主题包括编写包括 XML 数据的连接和 XMLTABLE 查询的窍门。
选择 SQL/XML vs. XQuery：它们有什么优缺点？
你可以在普通 XQuery、SQL/XML、或有嵌入式 SQL 的 XQuery 中表示很多查询。在一个特定情况下你可能发现这些选择中的一个比其它的更能直接的表达你的查询逻辑。通常情况下，查询 XML 数据的“正确”的方法需要根据具体情况来选择，要考虑到应用程序的需求和特点。不过，我们可以总结如下指南：
-
没有 XQuery 或 XPath 的普通 SQL在整个获取文档和操作和对整个文档的插入、删除、更新操作，文档的选择必须基于表中的非 XML 列。
-
SQL/XML 与 XQuery 或内置 XPath 的 SQL 语句提供了最广泛功能和最少的约束。你可以在 XML 列上表达谓词、抽取文档片段、想 XQuery 表达式传递变量标记、使用全文本搜索、聚集和对 SQL 级别分类，而且你可以联合并且以一个灵活的方式连接 XML 与关系型数据。这个方法适用大多数应用程序。
-
XQuery是一个非常强大的查询语言，专门针对查询 XML 数据设计。这样，如果你的应用程序需要查询并只操作 XML 数据而不涉及其它的关系型数据，这是一个非常好的选择。这在某些时候可能更简单也更直接。同样，如果你从一个纯 XML 数据库迁移到 DB2 9.5 并已经有现成的 XQueries 了，你可能更喜欢使用普通的 XQuery 。
-
XQuery 内置 SQL是一个很好的选择，如果你打算利用关系型谓词和索引以及全文搜索来从一个 XML 列中预过滤文档，把它们放入一个 XQuery 中。 SQL 嵌入 XQuery 也让你可以运行对 XML 列的外部函数。不过如果你需要用分组和聚集来执行数据分析查询的话，你可能更喜欢 SQL/XML 。
在下面的章节中，我们将用例子来说明每一个选项的有点和缺点。这个例子涉及到了前面介绍了的在衍生要以数据库中的样本文档。我们同样提供了一些获得你想要的输出格式的窍门。
SQL/XML(SQL 内置 XQuery/XPath)
SQL/XML 是一个 SQL 语言标准的一部分，它定义了一个 XML 数据类型和查询函数，构造函数、验证函数和转换 XML 数据函数。它的标准包括大量的发布的函数允许用户从关系数据构建 XML，也包括像 XMLQUERY、XMLTABLE 和 XMLEXISTS 谓词函数，这些构建函数允许用户在 SQL 语句中嵌入 XQuery 或简单的 XPath 描述。
在下面情况下使用 SQL/XML：
- 你有一个现有的 SQL 应用程序并需要添加一些这样那样的 XML 功能。
- 你是一个 SQL 的爱好者并希望把 SQL 作为主要的语言，因为这是你和你的团队最熟悉的语言。
- 你的查询需要同时从关系型列和 XML 列中返回数据。
- 你的查询需要通过 DB2 Net Search Extender（NSE）或 OmniFind 产品的全文本搜索环境。
- 你希望以结果集的形式返回并且缺少 XML 元素的用 nulls 表示。
- 你想使用参数标签。
这是一些使用 SQL/XML 的例子。
提示：XMLQUERY 函数通常在查询子句中被用来从 XML 列中抽取 XML 片段，虽然 XMLEXISTS 一般被用于 where 子句来表达 XML 数据的谓词。这里有一个例子显示了你可以如何以一个综合的方式使用 SQL/XML 来查询 XML 和关系型数据。 Select 子句可以从关系型和 XML 列中获取数据，并且 where 子句包含了关系型和 XML 两种谓词。
清单 16. SQL/XML 返回关系型和 XML 两种数据
select tradeId, xmlquery('$i/FpML/trade/tradeHeader/tradeDate/text()'
passing tradedoc as "i") as tradeDate
from trades
where xmlexists('$i/FpML/party[partyId="510026"]' passing tradedoc as "i")
and tradeId < 600; |
这个查询例子使用 XMLEXISTS 来查询 partyID 510026 发起的交易并应用 XMLQUERY 来从相应文档返回交易数据。结果就是：
TRADEID TRADEDATE
123 2001-04-29Z
456 2001-10-23Z |
你可通过在 XMLEXISTS 和 XMLQUERY 函数中忽略 PASSING 子句来以简化查询。如在如下面代码所示，你可以把列名作为 XQuery 表达式中的变量，而不需要 PASSING 子句。我们将从现在开始使用这个简化后的语法。
清单 17. 使用可选的公式来简化清单 16 中的查询
select tradeId,
xmlquery('$TRADEDOC/FpML/trade/tradeHeader/tradeDate/text()')
as tradeDate
from trades
where xmlexists('$TRADEDOC/FpML/party[partyId="510026"]')
and tradeId < 600; |
我们可以使用 XMLTABLE 函数来表达相同的查询，见清单 18 。在这个格式中，我们指定条件来约束输入数据并抽取我们感兴趣的输出值。在 XMLTABLE 函数中的 XQuery 表达指定了涉及 partyID510026 的交易和在 COLUMNS 子句（“ trade/tradeHeader/tradeDate ”）中的路径表达，以一个 SQL DATE 类型返回他们的交易数据。它的数据和清单 16，17 中的查询一样。这个 XMLTABLE 语法在 XQuery 表达中避免了 PASSING 子句。
清单 18. 清单 16 中的查询的变种 XMLTABLE
select t.tradeId, tx.tradeDate
from trades t,
XMLTABLE('$TRADEDOC/FpML[party/partyId="510026"]'
COLUMNS
tradeDate date path 'trade/tradeHeader/tradeDate' ) as tx
where t.tradeId < 600; |
提示：SQL/XML 擅长对 XML 的分组和聚集。 XQuery 语言没有提供一个明确的分组构造函数。随然分组和聚集可以在 XQuery 中使用自连接表达，但是这样做太笨拙了。你可以使用 SQL/XML 函数比如 XMLTABLE 或 XMLQUERY 来从 XML 列中抽取数据条目然后使用熟悉的 SQL 概念来再对它们加上分组和聚集的表达。
例如，让我们找出每个交易方都参加的交易号。清单 19 显示如何使用 XMLTABLE 来为后续 SQL 聚集和分组抽取 partyId 值。在 XMLTABLE 函数常常别产生相同结果，使用 SQL group by 和 聚集函数比使用普通 XQuery 得到相同的结果更有效。
清单 19. 使用 XMLTABLE 来分组和聚集
select T.partyId , count(*)
from trades,
xmltable('$TRADEDOC/FpML/party/partyId'
columns
partyId integer path '.' ) as T
group by partyId; |
结果是：
PARTYID COUNT
----------- -----------
67781 4
99114 3
510026 3 |
普通 XQuery
DB2 的所有主要应用程序编程接口（APIs）都支持 XQuery 作为首选语言，就像 SQL 一样。 XQuery 在如下情况有好处：
- 不需要使用 SQL 或关系型结构的 XML-only 应用程序。
- 从一个 XML-only 数据库迁移到 DB2 。现有的 XQueries 可以只需较小改动就在 DB2 中运行。例如，XQuery 的输入数据 , 来自 DB2 函数 db2-fn:xmlcolumn() 其它数据库可能叫做 collection() 。在这种情况下一只需要一个简单的重命名。
- 你可能发现 XQuery 表达两个 XML 文档的连接很方便，就像 unions XML 值一样。
XQuery 也有一些缺点：
- 使用普通 XQuery 你不能使用 DB2 Net Search Extender(NSE) 提供的全文本查询能力。要使用全文本查询你需要把引入 SQL 。
- 普通 XQuery 不允许你调用 SQL 用户定义函数（UDFs）或着用 C 或 Java 写的外部 UDFs 。
- 当前，DB2 没有提供一个方法来调用单独的有参数标记的 XQuery 。为了向 XQuery 传输参数，你必须使用 SQL/XML 来把参数标记 ("?") 转换成名称变量。
有内置 SQL 的 XQuery
XQuery 只允许你访问 XML 数据，而且仅限于 XML 数据。如果你只需要处理 XML 数据的话，这已经足够了，但是如果你的应用程序需要联合访问 XML 数据和关系型数据的话就显得不够了。但是使用 SQL/XML 可以，把 XQuery 嵌入 SQL 。相反的，把 SQL 嵌入 XQuery 提供了另外的可能性。
提示：内置 SQL 的 XQuery 允许你使用关系型谓词来约束对一个特定 XQuery 的输入以及只处理一个 XML 文档的子集。出于这个意图，DB2 提供了 db2-fn:sqlquery 函数来在 XQuery 中调用一个 SQL 查询。这个函数执行一个 SQL SELECT 语句并把一个返回的 XML 列作为输出。例如，清单 20 中的查询没有考虑在 XML 列 TRADEDOC 中的所有文档，不过有一个内置 SQL 语句将通过应用一个在关系型列 TRADEID 上的一个谓词来预先过滤 XML 文档。
清单 20. 使用 SQL 谓词约束对 XQuery 的输入
XQUERY
for $t in db2-fn:sqlquery("select tradedoc from trades t
where t.tradeId >= 789")/FpML/trade
where $t/termDeposit/principal/currency="EUR"
return $t/tradeHeader/tradeDate; |
一个在 TRADES 表中 TRADEID 列上的一个普通的关系型索引有助于加速内置 SQL 查询。
提示：DB2 VERSION 9.5 可以同时使用 XML 和关系型索引，就像内置 SQL 语句的关系索引加上 XML 谓词 $t/termDeposit/principal/currency= ” EUR ”的一个 XML 索引。
得到你想要的 XML 查询结果
根据你如何编写一个特定的查询，DB2 能以不同的格式传输查询结果。例如，普通 XQuery 在结果集中以每行一个条目来返回（比如元素或文档片段），即使有多个条目来自于在数据库中的相同文档。另一方面，SQL/XML 函数 XMLQUERY 可以在一行中返回多个条目。让我们看一些例子，在清单 21（SQL/XML）和清单 22（XQuery）中的查询都需要每个交易文档中 partyIds 。在清单 21 中的查询对表中的每个交易文档返回一行，而且在每一行中都包括文档中的两个 partyId 元素。来自相同文档的 partyIds 不能分成两行返回，因为这是一个 SQL select 语句，而且对每个匹配的输入行不能产生超过一行的输出：
清单 21. 每行一篇文档
select XMLQUERY('$TRADEDOC/FpML/party/partyId') from trades; |
结果集：
<partyId>510026</partyId><partyId>67781</partyId>
<partyId>510026</partyId><partyId>99114</partyId>
<partyId>99114</partyId><partyId>67781</partyId>
<partyId>510026</partyId><partyId>67781</partyId>
<partyId>99114</partyId><partyId>67781</partyId> |
另一方面，在清单 7 中的查询用分开的两行来返回每个 partyId 。这是因为一个 XQuery 返回一系列条目甚至多个源自于相同文档的条目。
清单 22. 每行一个 partyId
XQUERY db2-fn:xmlcolumn("TRADES.TRADEDOC")/FpML/party/partyId; |
结果集：
<partyId>510026</partyId>
<partyId>67781</partyId>
<partyId>510026</partyId>
<partyId>99114</partyId>
<partyId>99114</partyId>
<partyId>67781</partyId>
<partyId>510026</partyId>
<partyId>67781</partyId>
<partyId>99114</partyId>
<partyId>67781</partyId> |
虽然在清单 7 中的查询结果常常更容易被应用程序使用，（例如，一次一个 XML 值）但是有一个缺点是你不知道那些 partyId 元素来自于相同的交易文档。清单 21 中的查询输出通过成对显示 partyIds 保留了这个信息。如果应用程序使用一个 XML 解析器来处理来自 DB2 的每个 XML 结果行，来自于清单 21 的第一个结果行将被解析器拒绝，因为它不是一个良好格式的文档（它缺少一个根元素）。要解决这个问题，你可以像清单 8 一样添加一个单独的跟元素：
清单 23. 添加一个根元素
select XMLQUERY('<partyIdList>{$TRADEDOC/FpML/party/partyId}</partyIdList>')
from trades; |
这改变了查询结果，因此每个结果行都是一个有良好格式的 XML 文档。一个结果行的例子（总共有 5 个）是：
<partyIdList><partyId>99114</partyId><partyId>67781</partyId></partyIdList> |
如果你更喜欢在一个单独的行中获得每个 partyId，这可以通过在清单 24 中显示的 XMLTABLE 函数实现：
清单 24. 每行一个 partyId 值
select X.* from
trades,
XMLTABLE('$TRADEDOC/FpML/party'
COLUMNS
PartyId Varchar(20) PATH 'partyId') as X; |
结果集：
510026
67781
510026
99114
99114
67781
510026
67781
99114
67781 |
提示：通常情况下，如果你需要联合关系型和 XML 数据，那么在大多数情况下 SQL/XML 是最佳选择。尤其是，SQL/XML 是允许 XML 数据使用参数标签的一个选择。如果你有 XML-only 应用程序，单独的 XQuery 是一个强大的选择，而且可以用内置 SQL 来允许全文本查询和 UDFs 引用。
涉及 XML 数据的连接查询
提示：SQL/XML 谓词 XMLEXISTS 可以很容易连接 XML 数据和关系型数据。下面的例子使用一个 XMLTABLE 函数来选择交易在存款和取款期间它们的资本总量。这同时也获取这些交易的货币符号并用于和 CURRENCIES 表的关系型 SYMBOL 列来进行连接。最后，通过这个符号的流通值得到一个美元计数的本金来乘以存款的本金。这个查询使用 XMLEXISTS 来在一个关系值（CURRENCIES.SYMBOL）和一个 XML 值（/FpML/trade/termDeposit/principal/currency）执行一个连接。关系型列 SYMBOL 作为 $SYMBOL 在 XMLEXISTS 谓词中被参考：
清单 25. 用 SQL/XML 连接关系型数据和 XML 数据
select t.tradeid, c.symbol, c.USDvalue * tx.amount as principal_value_USD
from trades t, currencies c,
xmltable('$TRADEDOC/FpML/trade/termDeposit/principal'
COLUMNS
amount double path 'amount') as tx
where
xmlexists('
$TRADEDOC/FpML/trade/termDeposit/principal[currency = $SYMBOL]); |
查询结果是：
TRADEID SYMBOL PRINCIPAL_VALUE_USD
----------- ------ ------------------------
790 EUR +3.65000000000000E+007
791 EUR +7.30000000000000E+007
789 GBP +6.86000000000000E+007 |
如果在 currencies 表中的关系型列 SYMBOL 被定义为一个固定长度变量字符串，（例如，char(4) 而非 varchar(4) ），如果要让连接工作，我们需要删除后面所有的空格。我们可以通过编写这样的谓词：[currency = fn:normalize-space($SYMBOL)] 来实现。
为了完成这个连接，我们把货币符号传输到 XMLEXISTS 谓词中这样具体连接条件是一个 XQuery 谓词。相反的，我们也可以从 XML 数据中抽取主要的货币到 SQL 上下文中这样连接条件就是一个 SQL 谓词：
清单 26. 对 XML 数据使用 XMLCAST 和一个关系型连接谓词进行连接
select t.tradeid, c.symbol, c.USDvalue * tx.amount as principal_value_USD
from trades t, currencies c,
xmltable('$TRADEDOC/FpML/trade/termDeposit/principal'
COLUMNS
amount double path 'amount') as tx
where
c.symbol = XMLCAST(
XMLQUERY('$TRADEDOC/FpML/trade/termDeposit/principal/currency')
as char(3)); |
通常情况下，在清单 25 中的查询比在清单 26 中的更好。
提示：因为XMLCAST 函数期望单个输入值。如果在 XMLQUERY 中的路径表达返回不止一个数据值的情况下 XMLCAST 函数会失败。如果路径表达太简单就可能会发生这种情况，例如 $TRADEDOC/FpML/trade//currency，如果交易文档描述外汇交易事务的话这可能返回多个货币符号，因为它允许使用关系型索引（在这种情况下，在 CURRENCIES.SYMBOL 上）。在清单 25 中不能使用这个索引，因为连接条件不是一个关系型谓词而是一个 XQuery 谓词。
清单 27 中的查询连接了 PARTIES 和 TRADES 表的 XML 列，以返回这个交易中各方的实际名字。为了可以使用 SELECT DISTINCT 和消除重复的交易方名字，结果值被输出成一个 SQL VARCHAR 类型。
清单 27. 用 SQL/XML 连接 XML 数据
select distinct xmlcast(xmlquery('$PARTYINFO/Party/Name')as varchar(30))
from parties p, trades t
where XMLEXISTS('$PARTYINFO/Party[PtyID = $TRADEDOC/FpML/party/partyId]'); |
在普通 XQuery 也可以用两个嵌套的 FOR 子句表达相同的连接。你可以直接把它们想成在两个表之间的一个嵌套循环连接。
清单 28. 在两个 XML 文档之间的连接
XQUERY distinct-values(
for $tdoc in db2-fn:xmlcolumn("TRADES.TRADEDOC")/FpML
for $pty in db2-fn:xmlcolumn("PARTIES.PARTYINFO")/Party
where $tdoc/party/partyId=$pty/PtyID
return $pty/Name); |
在 XQuery 和 SQL/XML 查询中正确使用 * 和 //
通配符 * 和 // 在使用的时候需要小心，因为它们可能有性能影响并可能返回相当多的结果。无论你使用 XQuery 还是 SQL/XML 都会有很多不同的路径表达，这可能获取到期时间或某个交易的的本金总数。例如，/FpML/trade/termDeposit/principal/amount 和 //principal/amount 返回定期存款交易的本金总数。
为了获得最佳性能，比起 * 或 //，最好使用全路径，因为 DB2 可以跳过这个文档不相关的部分直接访问想要的元素。如果你用 //amount 替换 /FpML/trade/termDeposit/principal/amount，你访问的将是在这个文档中的所有“ amount ”元素。这需要 DB2 访问文档中的每一个分支，每一层的 principal/amount 元素，而这本是可以避免的开销。
注意 * 和 // 也可以导致不期望或意想不到的查询结果。例如，考虑上文中交易文档，它描述了一个涉及两个货币并使用不同“ amount ”元素的外汇交易。对于这个文档，路径 //amount 将返回
- … /fxSingleLeg/exchangedCurrency1/paymentAmount/amount 和
- … /fxSingleLeg/exchangedCurrency2/paymentAmount/amount 它们之间没有任何区别。尤其，一个 amount 是在人民币中另一个是在美元里，但是这个信息在应用程序简单请求 //amount 时候丢失了。
提示：总之，为了获得最佳的性能和有限的结果，你应该尽可能详细的指定你想要的元素的路径。
在 SQL/XML 查询中限制返回的行数和避免空行
在这个章节，我们显示如何在 SQL/XML 查询中正确的应用谓词，因此我们打算用它们来过滤一些行。假设你打算为清单 5 显示文档中所有的涉及 partyID 510026 的交易显示交易数据，就编写下面查询：
清单 29. XMLQUERY 使用谓词
select xmlquery('$TRADEDOC/FpML[party/partyId="510026"]/trade/tradeHeader/tradeDate')
from trades; |
出于多种原因，这不是你想要的查询：
- 它返回以下结果集有很多行，因为表中有很多交易的行不是你想要的交易方的。这是因为 SQL 语句没有 where 子句并因此不能出去任何一行。
<tradeDate>2001-04-29Z</tradeDate>
<tradeDate>2001-10-23Z</tradeDate>
<tradeDate>2001-04-29Z</tradeDate>
5 record(s) selected |
在这个表中的每一行和谓词不匹配，一行有一个空的 XML 顺序返回。这是因为在 XMLQUERY 函数中的 XQuery 表达一次应用到一行（文档）并且不会从结果集中删除行，而只更改它的值。如果谓词是真，那么 XQuery 生成的值不是 tradeData 元素就是空序列。如果查询是以这种方式编写的，这些空行在语义上是正确的（根据 SQL/XML 标准）而且必须被返回 。
- 这个查询的性能不会很好。首先，在 /FpML/party/partyId 上可能已经存在一个索引而不能使用，因为这个查询不允许出去任何一行。其次，返回大量的空行使这个查询毫无必要的变慢。
提示：要解决这个性能问题并获得期望的输出，你应该在 SELECT 子句中使用 XMLQUERY 函数来只抽取交易数据，并改变搜索条件，在 WHERE 子句中用一个 XMLEXISTS 谓词来消除一些行。这将允许所用使用行过滤并避免空结果行的开销。用以下这些方法来编写语句只返回期望的行。
清单 30. XMLQUERY 谓词使用 XMLEXISTS 来过滤行
select xmlquery('$TRADEDOC/FpML/trade/tradeHeader/tradeDate ' )
from trades
where xmlexists('$TRADEDOC/FpML[party/partyId="510026"]' ); |
结果集
<tradeDate>2001-04-29Z</tradeDate>
<tradeDate>2001-10-23Z</tradeDate>
<tradeDate>2001-04-29Z</tradeDate>
3 record(s) selected |
总而言之，XMLQUERY 函数中的谓词只应用在每个 XML 值中，所以它们永远不会删除任何一行。文档和行过滤谓词都应该使用 XMLEXISTS 函数。
提示：然而，在 XMLEXISTS 函数中发生错误而导致不可预知结果的可能性仍然存在。例如，一个的在之前查询的语句中 XMLEXISTS 函数中未写方括号的一般错误。
清单 31. 在谓词中忘记括号
select xmlquery('$TRADEDOC/FpML/trade/tradeHeader/tradeDate ' )
from trades
where xmlexists('$TRADEDOC/FpML/party/partyId = "510026"' ); |
这生成和清单 14 相同的结果，包括所有不期望的空行。没有方括号 XMLEXISTS 谓词总是被预判为真。因此，不会有行被删除。这是因为对于一个给定的行，只有 XQuery 表达式处返回空序列时 XMLEXISTS 谓词才判断为假。然而，没有方括号 XQuery 表达式是一个布尔表达式它总是返回布尔值而非空序列。注意，XMLEXISTS 会检查一个值是否存在，如果值存在就为真，即使这个值被设置为布尔值“假”。根据 SQL/XML 标准，这也是一个正确的行为，虽然这不是你想表达的。
类似的如清单 17 所示，在使用两个或多个谓词的时候，要小心不要犯相同的错误。
清单 32. 错误使用“ and ”
select xmlquery('$TRADEDOC/FpML/trade/tradeHeader/tradeDate ')
from trades
where xmlexists('
$TRADEDOC/FpML/party[partyId="510026"] and
$TRADEDOC/FpML/trade/termDeposit[startDate="2002-02-14Z"]'
); |
这个查询用了方括号，所以有什么问题？ XQuery 表达式仍然是一个布尔表达，因为它的形式是“ exp1 and exp2 ”，并因此再一次返回了所有的行。这里是一个正确的方法来编写查询语句以过滤行并允许使用索引。
清单 33. 正确的语句
select xmlquery('$TRADEDOC/FpML/trade/tradeHeader/tradeDate ')
from trades
where xmlexists('$TRADEDOC/FpML[party/partyId="510026" and
trade/termDeposit/startDate="2002-02-14Z"]'); |
总之，不要在 XMLEXISTS 中使用布尔值谓词。把谓词放入方括号，包括所有“ and ”和“ or ”。更多指南和 XML 查询例子，见【 1 】
编写 XMLTABLE 查询
XML 表函数有很多种用法，在联合 XML 数据和关系型数据时或者为 XML 数据创建关系视图时这尤为重要。我们将回顾 XMLTABLE 的基础并给出一些使用的窍门。这有一个 XMLTABLE 的例子，这个例子获取来自于 TRADES 表中所有交易的信息。
清单 34. 一个 XMLTABLE 查询
Select t.*
from trades, xmltable('$TRADEDOC/FpML'
columns
tradeType varchar(20) path 'trade/*[2]/local-name(.)',
tradeDate date path 'trade/tradeHeader/tradeDate',
partyId1 integer path 'party[@id="party1"]/partyId' ,
partyId2 integer path 'party[@id="party2"]/partyId'
) as t; |
这个查询产生下面输出：
TRADETYPE TRADEDATE PARTYID1 PARTYID2
-------------------- ---------- ----------- -----------
bulletPayment 04/29/2001 510026 67781
fxSingleLeg 10/23/2001 510026 99114
termDeposit 02/14/2002 99114 67781
termDeposit 04/29/2001 510026 67781
termDeposit 03/26/2002 99114 67781 |
回想 SELECT 语句对被操作表（TRADES）的 FROM 子句中使用 XMLTABLE 函数。 XMLTABLE 函数是隐含的加入 TRADES 表并被应用于它的所有行。在 COLUMNS 子句中 XMLTABLE 函数包含只row-generating的 XQuery 表达和一个或多个column-generating的表达。在清单 34 中，生成行的表达式是 XPath$TRADEDOC/FpML 。
在 XML 列中 row-generating 表达应用于每一个 XML 文档并为每个文档生成一个或多个 FpML 元素（sub-tree）。 XMLTABLE 函数的输出对每个 FpML 元素有一行。因此，row-generating XQuery 表达式生成的输出确定 SELECT 语句返回的结果集的基数。
COLUMNS 子句把 XML 数据转换成一个有名字和 SQL 数据类型的关系型数据列。在清单 34 中，返回的行由 4 列– tradeType、tradeDate、partyId1 和 partyId2 。 row-generating 表达式为 column-generating 表达式提供了上下文关。因此通常情况下，为了得到一个对 XMLTABLE 函数从这列中返回了什么的直观的看法你可以在 row-generating 中添加 column-generating 。
记住在 COLUMNS 子句中的路径表达一定不要一行返回超过一个条目。如果一个路径表达返两个或者更多条目的序列， XMLTABLE 的执行会失败，因为它不能转换一批 XML 值到一个原子的 SQL 值中。这个场景将在后面讨论。
为你数据的子集生成 XMLTABLE 行
默认情况下，XMLTABLE 的 row-generating 表达式在指定的列上应用到每个 XML 文档。然而，你可能想只为这个文档的一个基于某些过滤谓词的子集生成行。例如假设你想更改清单 34 中的 XMLTABLE 并只为涉及 partyId 99114 的交易生成行。你可以使用一个 WHERE 子句通过两种方式来表达这个约束，如清单 35 所示。其中一个可能是 WHERE 子句是被注释掉了，因为它不是一个好的实践。如果你在这个由 XMLTABLE 生成的列上定义过滤谓词，XMLTABLE 函数必须为所有的文档生成行，就算它们不是安全的谓词。这些谓词会在 XMLTABLE 完成了它自己的工作后被应用，这对性能没有好处。
清单 35. 用 XMLEXISTS 来约束 XMLTABLE
select t.*
from trades, xmltable('$TRADEDOC/FpML'
columns
tradeType varchar(20) path 'trade/*[2]/local-name(.)',
tradeDate date path 'trade/tradeHeader/tradeDate',
partyId1 integer path 'party[@id="party1"]/partyId' ,
partyId2 integer path 'party[@id="party2"]/partyId'
) as t
-- not good: where partyId1 = 99114 or partyId2 = 99114
where xmlexists('$TRADEDOC/FpML/party[partyId=99114]'); |
提示：更好的方法是使用 XMLEXISTS 来表达在 XML 列上的过滤条件。这限制了文档输入 XMLTABLE 函数。另外，在清单 36 中的查询在 row-generating 表达式中应用了 XML 谓词，在这种情况下 XMLEXISTS 不再需要。它返回相同的结果并具有同样的性能。
清单 36. 在 row-generating 表达中约束 XMLTABLE
select t.*
from trades, xmltable('$TRADEDOC/FpML[party/partyId=99114]'
columns
tradeType varchar(20) path 'trade/*[2]/local-name(.)',
tradeDate date path 'trade/tradeHeader/tradeDate',
partyId1 integer path 'party[@id="party1"]/partyId' ,
partyId2 integer path 'party[@id="party2"]/partyId'
) as t; |
在 XMLTABLE 查询中处理重复路径
在 COLUMNS 子句中的路径表达必须确保每一行产生不超过一个条目，否则 XMLTABLE() 函数将会失败。然而，合理的情况是一行可能产生多个条目，而且你需要改变查询处理他们。如第一部分中清单 3 所示，请考虑在 PARTIES 表中的文档。一个交易方的文档可以包含一个或多个元素来表述它的汇率和日期。如果你想查询这个数据并返回一个有各方基本信息的关系表，你可能需要编写如下查询：
清单 37. 因为多个 Ratings，XMLTABLE 将返回一个错误
select p.*
from parties, xmltable('$PARTYINFO/Party'
columns
PartyID integer path 'PtyID',
ShortName varchar(10) path 'ShortName',
Rating varchar(6) path 'Rating/RatingValue') as p; |
对于本文中使用的样本文档，这个查询将会失败并抛出下面信息：
SQL16003N An expression of data type "( item(), item()+ )"
cannot be used when the data type "VARCHAR_6" is expected in the context. |
这个信息的含义就是查询试图在一个 VARCHAR 值中嵌套一个有多个条目的 XML 序列。“(item(), item()+) ”数据类型值意味着一个条目后面有一个过多个多余条目。简单的说，就是这个值是两个或两个以上的序列。这是因为路径表达式“ Rating/RatingValue ”对“ YourWorld Investments ”返回了两个 RatingValue 元素。
有多种方法处理这个问题。如果你只想看到第一个 <Rating> 元素，你可以使用一个位置谓词来指定返回的 RatingValue 只是第一 Rating 元素：
清单 38. 只返回第一个 Rating 元素
select p.*
from parties, xmltable('$PARTYINFO/Party'
columns
PartyID integer path 'PtyID',
ShortName varchar(10) path 'ShortName',
Rating varchar(6) path 'Rating[1]/RatingValue') as p; |
这个查询返回：
PARTYID SHORTNAME RATING
----------- ---------- ------
510026 MIB Baa1
67781 NVB Aa
99114 YWI Aaa |
类似的，如果你想总是返回头两个 Rating 元素。你可以为它们明确定义列。如果一方只有一个汇率，那么第二列（“ RatingPrior ”）将会是空。除非你为它定义一个默认值。清单 39 显示了如何实现：
清单 39. 返回头两个汇率，在第二个值不显示的情况下使用默认值
select p.*
from parties, xmltable('$PARTYINFO/Party'
columns
PartyID integer path 'PtyID',
ShortName varchar(10) path 'ShortName',
Country varchar(10) path 'Address/Country',
RatingRecent varchar(6) path 'Rating[1]/RatingValue',
RatingPrior varchar(6) default ‘ NONE ’ path ‘ Rating[2]/RatingValue ’
) as p; |
这个查询的的输出：
PARTYID SHORTNAME RATINGRECENT RATINGPRIOR
----------- ---------- ------------ -----------
510026 MIB Baa1 NONE
67781 NVB Aa NONE
99114 YWI Aaa Aa |
对设计各方的大型交易，如果有多个汇率，你可能想要考虑返回所有的汇率，要么用 VARCHAR 包含一个用逗号分隔的列表，或者作为包含一个序列的一个 XML 列。清单 40 显示了这两个选项：
清单 40. 用以逗号分隔或一个 XML 序列来返回多个汇率
select p.*
from parties, xmltable('$PARTYINFO/Party'
columns
PartyID integer path 'PtyID',
Short varchar(10) path 'ShortName',
Country varchar(10) path 'Address/Country',
Rating_v varchar(6) path 'fn:string-join(Rating/RatingValue/text(),",")',
Rating_X XML path 'Rating/RatingValue')
as p; |
返回结果是：
PARTYID SHORT RATING_V RATING_X
------- ----- -------- --------
510026 MIB Baa1 <RatingValue>Baa1</RatingValue>
67781 NVB Aa <RatingValue>Aa</RatingValue>
99114 YWI Aaa,Aa <RatingValue>Aaa</RatingValue><RatingValue>Aa</RatingValue> |
更多使用 XMLTABLE 函数的窍门和手段在【 5 】和【 6 】中提供。
在 XML 数据上使用关系型视图
可以定义一视图以关系型格式暴露 XML 数据，这样做非常有用。让我们来看看下面的例子，在这个例子中 SQL/XML 函数 XMLTABLE 被用来以表的形式从 TRADES 表中的 XML 文档返回值。视图从原始 XML 文档中就像处理关系列一样抽取交易 ID、交易数据和 aprtyID 信息。
清单 41. 使用 XMLTABLE 在 XML 数据上建立关系视图
create view tradesv (tradeId , tradeDate, partyId1 , partyId2 , tradedoc) as
select tradeId, t.*, tradedoc
from trades, xmltable('$TRADEDOC/FpML'
columns
tradeDate date path 'trade/tradeHeader/tradeDate',
partyId1 integer path 'party[@id="party1"]/partyId',
partyId2 integer path 'party[@id="party2"]/partyId'
) as t; |
查询这个视图的所有列，除了 XML 文档 tradedoc 的结果是：
TRADEID TRADEDATE PARTYID1 PARTYID2
----------- ---------- ----------- -----------
123 04/29/2001 510026 67781
456 10/23/2001 510026 99114
789 02/14/2002 99114 67781
790 04/29/2001 510026 67781
791 03/26/2002 99114 67781
5 record(s) selected. |
提示：在这个视图的定义中我们包括了 XML 列的信息，这些信息有助于更有效的查询这个视图。对一个给定的 partyId 假设你想获取一个 tradeIds 和 aprtyId 对的列表。下面的两个查询都可以做到这点。在清单 42 中的查询使用一个在关系型 INTEGER 列 partyId1 上由关系型视图 TRADESV 所暴露出来的谓词。
清单 42. 使用关系型谓词来查询关系型视图
select tradeId , tradeDate, partyId1 , partyId2 from tradesv
where partyid1 = 99114 or partyid2 = 99114; |
这是一个很自然并动人的构想，这个查询和清单 35 中的一个非常像，在 XMLTABLE 视图上过滤关系型谓词不能应用到底层 XML 列或索引上。编写这个查询的更好的方法是对 TRADESV 视图暴露出来的文档使用一个 XMLEXISTS 谓词：
清单 43. 使用 XMLEXISTS 谓词查询关系型视图
select tradeId , tradeDate, partyId1 , partyId2 from tradesv where
XMLEXISTS ('$TRADEDOC/FpML/party[partyId="99114"]'); |
这样做确保了视图只生成这些有 partyId “ 99114 ”的行、并产生更短的运行时间，尤其是对于大型数据集合。
参考资料 学习
获得产品和技术
-
现在可以免费使用 DB2。下载 DB2 Express-C，这是为社区提供的 DB2 Express Edition 的免费版本，它提供了与 DB2 Express Edition 相同的核心数据特性，为构建和部署应用程序奠定了坚实的基础。
- 下载 信息管理软件试用版，体验它们强大的功能。
讨论
关于作者  | | | developerWorks 中国网站编辑团。 |
对本文的评价
| 
