IBM Support

在IBM i的关系数据世界中使用XML

Technical Blog Post


Abstract

在IBM i的关系数据世界中使用XML

Body

原文链接:http://ibmsystemsmag.blogs.com/i_can/2012/05/i-can-use-xml-in-a-relational-world.html


这篇博客由Nick Lawrence写作,Nick工作于明尼苏达州罗彻斯特的DB2 for IBM i团队,他的职责包括DB2和SQL/XML的全文索引功能。感谢Nick!

在最近一轮的发布中,IBM提供了在SQL中对XMLTABLE表函数的支持。XMLTABLE通过使用时下流行的XPath表达式,可以实现XML文档到关系结果集(行和列)的转换。由于XMLTABLE可以帮助解决各种XML相关的问题,因此它也被誉为XML应用的“瑞士军刀”。

在信息中心中有一本新书——SQL XML Reference,可以作为学习XMLTABLE的一个不错的教程,里面介绍了很多XMLTABLE如何在DB2的XML列中查询数据的例子。

从相关文档中我们不难发现,DB2 for i对XML的支持,给那些支持Web的应用程序带来了极大的便利,即使数据库中的所有数据都保存在一个纯关系模型中,我们依然可以方便地处理。

举个例子,假设我用以下语句创建了一个SQL表:

create table orders(
     order_id bigint generated always as identity
              (start with 1000 increment by 1),
     cust_email varchar(255),
     order_ts    timestamp,
     product     varchar(255),
     primary key (order_id));

然后插入以下行:

insert into orders (cust_email, order_ts, product)
values
('ntl@us.ibm.com',
 '2012-04-15 13:00:00',
 'Camera'),

('ntl@us.ibm.com',
 '2012-04-16 12:00:00',
 'lens'),

('ntl@us.ibm.com',
 '2012-04-01 11:00:00',
 'Book'),

('george@nowhere.com',
 '2012-04-15 13:05:00',
 'Book') ;

我的应用程序将收到一个XML格式的Web请求,其中包含一封客户的邮件和日期范围。应用程序期望能够在上表中查询并且返回XML格式的匹配订单。

为简单起见,假设我已经从Web中得到了我请求的XML文件,并且保存在一个全局变量中:

create or replace variable  order_request xml;

set order_example.order_request = xmlparse(document
'<OrderInfoRequest>
   <CustEmail>ntl@us.ibm.com</CustEmail>
   <MinTs> 2012-04-14T00:00:00 </MinTs>
   <MaxTs> 2012-04-30T23:59:59 </MaxTs>
</OrderInfoRequest>' );

对于以上的样本数据和请求来说,我们假设一个有效的响应需要采用如下结构:

<?xml version="1.0" encoding="UTF-8"?>
<InfoRequestResponse>
    <MatchingOrder>
        <OrderId>1000</OrderId>
        <CustEmail>ntl@us.ibm.com</CustEmail>
        <OrderTs>2012-04-15T13:00:00.000000</OrderTs>
        <Product>Camera</Product>
    </MatchingOrder>
    <MatchingOrder>
        <OrderId>1001</OrderId>
        <CustEmail>ntl@us.ibm.com</CustEmail>
        <OrderTs>2012-04-16T12:00:00.000000</OrderTs>
        <Product>lens</Product>
    </MatchingOrder>
</InfoRequestResponse>

如果没有7.1中的SQL/XML支持,这将会是一个复杂的问题,我们需要一个SQL查询和一些外部代码来处理XML的特性。但是现在我们只需要一个简单的SQL查询就可以完成同样的工作。

首先需要明确的是我们可以将这个SQL表与一个由XMLTABLE调用产生的结果集连接起来。

接下来使用以下SQL查询,可以得到与XML文件OrderInfoRequest中的说明相符合的行:

select   ORDER_ID,
         CUST_EMAIL,
         ORDER_TS,
         PRODUCT
from
orders,
xmltable('OrderInfoRequest'
  passing order_example.order_request
  columns  "CustEmail"  varchar(255),
           "MinTs"      Timestamp,
           "MaxTs"      Timestamp
) info_request
where
 orders.cust_email = info_request."CustEmail" and
 orders.order_ts >= info_request."MinTs" and
 orders.order_ts <= info_request."MaxTs";

将以上的查询和SQL发布函数结合起来就可以创建出我们期望的XML响应文件,其中查询产生的每一行被转换成“MatchingOrder” 元素,而剩余部分则围绕在这些值周围。这看起来有点复杂,有个诀窍是,使用普通的表表达式和XML聚集函数来创建那些重复的内部值,“由里到外”地构建文档。

我们只需要以下的查询就可以满足应用程序的需求:

-- intermediate result set of
-- “MatchingOrder” Elements
with matching_orders as (
 select
  XMLELEMENT(NAME "MatchingOrder",
             XMLFOREST(ORDER_ID AS "OrderId",
                       CUST_EMAIL AS "CustEmail",
                       ORDER_TS AS "OrderTs",
                       PRODUCT AS "Product")
             ) AS ORDER
 from orders,
      xmltable('OrderInfoRequest'
               passing order_example.order_request
               columns "CustEmail" varchar(255),
                       "MinTs"     Timestamp,
                       “MaxTs"     Timestamp
               ) info_request
 Where
  orders.cust_email = info_request."CustEmail" and
  orders.order_ts >= info_request."MinTs" and 
  orders.order_ts <= info_request."MaxTs"
)

-- build InfoRequestResponse element
-- around matching orders
select
XMLSERIALIZE(
  XMLDOCUMENT(
      XMLELEMENT(NAME "InfoRequestResponse",
                 XMLAGG(matching_orders.ORDER)
      )
  ) AS CLOB(1G) CCSID 1208 INCLUDING XMLDECLARATION
 )
from matching_orders;

读者可能已经注意到了XML  xs:dateTime 对SQL时间戳来说并不是一个合法的语句格式(反之亦然),的确,这对于手动编写的解决方案来说是一个讨厌的问题。不过SQL/XML函数自动为我们处理了XML样式和SQL样式之间的转换,用SQL/XML来设计代码可以保证结果有良好的XML结构。

最后,虽然学习使用XML函数的周期可能比较长,但是从长远来看,使用这个新支持的功能,我们可以更加方便地在应用程序中采用Web技术。同时由于避免了在SQL查询中编写特殊代码来包含XML数据,因此随着面向web的组件的发展,修改和扩展应用程序也变得更加简单。


以下最新发表的文章可以帮助我们更好地了解XML数据模型以及它对DB2带来的影响:
http://www.ibmsystemsmag.com/ibmi/developer/general/xml_db2_part1/
http://www.ibmsystemsmag.com/ibmi/developer/general/xml_db2_part2/

XMLTABLE发布链接:
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/IBM%20i%20Technology%20Updates/page/XMLTABLE?lang=en


原文作者:Nick Lawrence
翻译作者:陈杰 

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11145212