从 XML 消息和数据库表中连接数据

可以使用与消息数据和数据库都进行交互的 SELECT 语句。 还可以嵌套 SELECT 语句,该语句与另一个 SELECT 语句中的一种数据类型进行交互,而该另一个 SELECT 语句又与其他类型的数据进行交互。

请参阅下列包含两个客户发票信息的输入消息:

<Data>
<Invoice>
<CustomerNumber>1234</CustomerNumber>
<Item>
<PartNumber>1</PartNumber>
<Quantity>9876</Quantity>
</Item>
<Item>
<PartNumber>2</PartNumber>
<Quantity>8765</Quantity>
</Item>
</Invoice>
<Invoice>
<CustomerNumber>2345</CustomerNumber>
<Item>
<PartNumber>2</PartNumber>
<Quantity>7654</Quantity>
</Item>
<Item>
<PartNumber>1</PartNumber>
<Quantity>6543</Quantity>
</Item>
</Invoice>
</Data>

请参阅下列数据库表 Prices 和 Addresses 以及它们的内容:

PARTNO      PRICE                   
----------- ------------------------
          1            +2.50000E+001
          2            +6.50000E+00
PARTNO      STREET                CITY             COUNTRY  
------ ------------------- -------------- -------
1234        22 Railway Cuttings   East Cheam       England
2345        The Warren            Watership Down   England

如果您对下列 ESQL 转换进行编码:

-- Create a valid output message
SET OutputRoot.MQMD = InputRoot.MQMD;
-- Select suitable invoices
SET OutputRoot.XML.Data.Statement[] =
   (SELECT I.CustomerNumber                         AS Customer.Number,
           A.Street                                 AS Customer.Street,
           A.City                                   AS Customer.Town,
           A.Country                                AS Customer.Country,
        -- Select suitable items
          (SELECT II.PartNumber AS PartNumber,
            II.Quantity   AS Quantity,
                  PI.Price      AS Price
           FROM Database.db2admin.Prices AS PI,
               I.Item[]                 AS II
           WHERE II.PartNumber = PI.PartNo     )    AS Purchases.Item[]
    FROM Database.db2admin.Addresses  AS A,
         InputRoot.XML.Data.Invoice[] AS I
    WHERE I.CustomerNumber = A.PartNo
);

生成下列输出消息:输入消息已增加了从数据库表中获取的价格和地址信息:

<Data>
<Statement>
<Customer>
<Number>1234</Number>
<Street>22 Railway Cuttings</Street>
<Town>East Cheam</Town>
<Country>England</Country>
</Customer>
<Purchases>
<Item>
<PartNumber>1</PartNumber>
<Quantity>9876</Quantity>
<Price>2.5E+1</Price>
</Item>
<Item>
<PartNumber>2</PartNumber>
<Quantity>8765</Quantity>
<Price>6.5E+1</Price>
</Item>
</Purchases>
</Statement>
<Statement>
<Customer>
<Number>2345</Number>
<Street>The Warren</Street>
<Town>Watership Down</Town>
<Country>England</Country>
</Customer>
<Purchases>
<Item>
<PartNumber>1</PartNumber>
<Quantity>6543</Quantity>
<Price>2.5E+1</Price></Item>
<Item>
<PartNumber>2</PartNumber>
<Quantity>7654</Quantity>
<Price>6.5E+1</Price>
</Item>
</Purchases>
</Statement>
</Data>

可以在消息 SELECT 语句中嵌套数据库 SELECT。在多少情况下,这不然前一个示例有效,但是您可能发现如果消息小而数据库表大,这个比较好。

-- Create a valid output message
SET OutputRoot.MQMD = InputRoot.MQMD;
-- Select suitable invoices
SET OutputRoot.XML.Data.Statement[] =
   (SELECT I.CustomerNumber                         AS Customer.Number,
        -- Look up the address
        THE ( SELECT 
                  A.Street,
                  A.City    AS Town,
                  A.Country
                FROM Database.db2admin.Addresses AS A
                WHERE A.PartNo = I.CustomerNumber
            )                             AS Customer,
        -- Select suitable items
       (SELECT
            II.PartNumber AS PartNumber,
            II.Quantity   AS Quantity,
            -- Look up the price
            THE (SELECT ITEM P.Price
              FROM Database.db2admin.Prices AS P
              WHERE P.PartNo = II.PartNumber
            )             AS Price               
          FROM I.Item[] AS II           ) AS Purchases.Item[]
     FROM InputRoot.XML.Data.Invoice[] AS I
);
相关概念
消息流概述
ESQL 概述
消息建模
相关任务
设计消息流
定义消息流内容
管理 ESQL 文件
相关参考
Compute 节点
Database 节点
Filter 节点
ESQL 引用
SELECT 函数
SET 语句
声明 | 商标 | 下载 | | 支持 | 反馈
Copyright IBM Corporation 1999, 2006 最后更新:2006/05/19
ak05780_