DB2 9:XML数据和关系数据相互转化-技术开发专区

DB2 9:XML数据和关系数据相互转化

作者：IT168 gmplayer 编辑： ITPUB实习编辑 2007-03-04 21:39

【IT168技术文档】
    我们知道DB2 9中的重要特性是最新支持了对 XML 数据的存储、管理和搜索。用户可以用一个数据库对象同时管理 “传统的” SQL 数据和 XML 文档。而且，可以编写一个查询同时搜索和处理这两种数据形式。在不同的系统和应用程序之间交换数据并适应快速变化的业务环境。许多 IT 领导者正在寻找办法高效地共享、搜索和管理公司产生的大量 XML 文档和消息。DB2 新的 XML 支持的设计目的就是为了帮助公司减少存储和使用 XML 数据所需的时间和工作量，进而减少开发费用并提高业务灵活性。

    在实际的应用中，我们难免会将关系数据和XML数据混合起来进行处理，以应对灵活的需求，如我们可能将存储在DB2中的XML数据转化为关系数据来进行处理，还有可能我们会将关系数据转化为XML数据来进行处理。在具体的开发中，如何将这中转化灵活有机的结合起来，需要我们对DB2的SQL/XML和XQuery有足够的了解。

    正是因为DB2 的混合体系结构与之前的版本有很大的不同，但是要利用它的新 XML 功能并不难。如果您已经熟悉 SQL，那么很快就可以将这方面的技能转化到对存储在 DB2 中的本地 XML 数据的处理上。本文我们将为您介绍在DB2中关系数据和XML数据如何的进行相互的转化。

    准备工作：
    本文所进行的各种查询操作都是在SAMPLE数据库中。在SAMPLE数据库中创建CUSTOMERS（客户表）：

CREATE TABLE ADMINISTRATOR.CUSTOMERS ( CUSTOMERID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1001, INCREMENT BY 1, NO CACHE ) , NAME VARCHAR (10) , AGE BIGINT , COMMPANY VARCHAR (10) , CONTACTINFO XML ) ;

    该客户表中，包括CUSTOMERID（客户编号）、NAME(客户姓名)、AGE（年龄）、COMMPANY（公司）和CONTACTINFO（客户联系方式）几个字段。

    在CUSTOMERS表中插入3条记录：

其中CONTACTINFO（客户联系方式）字段为XML数据类型，其中XML数据为：

<?xml version="1.0"?>
<Customer xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance  
      xsi:noNamespaceSchemaLocation="http://bogus">
    <Address>
        <street>5401 Julio Ave.</street>
        <city>San Jose</city>
        <state>CA</state>
        <zip>95116</zip>
    </Address>
    <phone>
        <work>4084630000</work>
        <home>4081111111</home>
        <cell>4082222222</cell>
    </phone>
    <fax>4087776666</fax>
    <email>love2shop@yahoo.com</email>
</Customer>

    当然一个CONTACTINFO字段对应着一个XML数据。

【IT168技术文档】
    从XMl数据产生表格来产生关系数据
    在上述给出的实例中，我们可能有这样的需求，查询客户的姓名，年龄和传真。这里姓名和年龄是关系数据，而传真是存储在XML字段中的，属于XML数据。虽然我们通过SQL/XML能够来完成。但是如果频繁的进行类似的查询，可能会显得不是很方便。我们可以通过创建视图来建一张关系数据表，即形成我们按照纯关系数据库来建立的客户表。其形式可能为：

这里，实际上我们就是要检索多个 XML 元素并将每个元素转换成传统的 SQL 数据类型。

为了实现这样的形式，我们可以使用SQL/XML语句来实现。

select i.NAME, i.AGE,t.fax,t.email from CUSTOMERS i  ,
xmltable('$c/Customer' passing i.CONTACTINFO as "c"
columns fax varchar(100) path 'fax',
    email varchar(100) path 'email'
      ) as t
where NAME='Smith'

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

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

SQL/XML 函数可用于定义视图。如果要为 SQL 应用程序的程序员提供本地 XML 数据的关系模型，那么这种功能特别有用。
为 XML 列中的数据创建关系视图并不比投影 XML 元素值复杂多少。您只需编写一个 SQL/XML SELECT 语句，在语句中调用 XMLTable 函数，并以此作为视图定义的基础。然后我们在通过创建视图来产生关系数据表：

Create view CustomerView(Name, Age, Fax, Email) as
select i.NAME, i.AGE,t.fax,t.email from CUSTOMERS i  ,
xmltable('$c/Customer' passing i.CONTACTINFO as "c"
columns fax varchar(100) path 'fax',
    email varchar(100) path 'email'
      ) as t
where NAME='Smith'

这样就产生了CustomerView视图

    接下来，我们就可以像操作关系数据表那样来操作CustomerView进行各种查询操作

【IT168技术文档】
    将关系数据发布为 XML
    SQL/XML 还提供了其他非常方便的特性。其中一个特性是将关系数据转换或发布为 XML。可以通过SQL/XML 函数：XMLElement、XMLAgg 和 XMLForest来实现。

通过 XMLElement 可以将存储在传统的 SQL 列中的数据转换成 XML 片段。也就是说，可以基于基本的 SQL 数据构造 XML 元素（带 XML 属性或者不带 XML 属性）。下面的例子嵌入了 XMLElement 函数来创建一系列的 customer 元素，每个 customer元素包含一些子元素，分别存放从 " CUSTOMERS " 表获得的 CUSTOMERID、NAME和AGE值：

使用 XMLElement 将关系数据发布为 XML

select xmlelement (name "customer",
    xmlelement (name "id", CustomerID),
    xmlelement (name "name", NAME),
    xmlelement (name "age", AGE) ) from CUSTOMERS
where NAME='Smith'

运行该查询将产生类似以下的结果：

<customer>
  <id>1006</id>
  <name>Smith</name>
  <age>45</age>
</customer>

还可以将 XMLElement 与其他 SQL/XML 发布函数结合使用来构造 XML 值以及将这些值分组，使它们嵌套成一定的层次结构。这里就不多说了。

关注我们