转换复杂 XML 消息

当您为 Compute 节点对 ESQL 进行编码时,请使用 SELECT 语句进行复杂消息的转换。

示例

查看以下示例并对其进行修改以供您自己使用。它们都作为输入而基于发票消息:

在此示例中,发票包含可变数目的项。转换如下所示:

SET OutputRoot.XML.Data.Statement[] =
        (SELECT I.Customer.Title                                     AS Customer.Title, 
                        I.Customer.FirstName || ' ' || I.Customer.LastName   AS Customer.Name,
                        COALESCE(I.Customer.PhoneHome,'')                    AS Customer.Phone,
            (SELECT II.Title                          AS Desc,
                                        CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost,
                               II.Quantity                     AS Qty
                          FROM I.Purchases.Item[] AS II
                          WHERE  II.UnitPrice > 0.0              )        AS Purchases.Article[],
                        (SELECT SUM( CAST(II.UnitPrice AS FLOAT) *
                                    CAST(II.Quantity AS FLOAT) *
                                    1.6                          )
                          FROM I.Purchases.Item[] AS II      )                AS Amount,
                   'Dollars'                                   AS Amount.(XML.Attribute)Currency
            
                        FROM InputRoot.XML.Invoice[] AS I
                        WHERE I.Customer.LastName <> 'Brown'
    );

生成的输出消息是:

<Data>
 <Statement>
  <Customer>
   		 		 <Title>Mr</Title>
   <Name>Andrew Smith</Name>
   <Phone>01962818000</Phone>
  </Customer>
  <Purchases>
   <Article>
        <Desc Category="Computer" Form="Paperback" Edition="2">The XML Companion</Desc>
    <Cost>4.472E+1</Cost>
    <Qty>2</Qty>
   </Article>
   <Article>
    <Desc Category="Computer" Form="Paperback" Edition="2">
          A Complete Guide to DB2 Universal Database</Desc>
    <Cost>6.872E+1</Cost>
    <Qty>1</Qty>
   </Article>
   <Article>
        <Desc Category="Computer" Form="Hardcover" Edition="0">JAVA 2 Developers Handbook</Desc>
    <Cost>9.5984E+1</Cost>
    <Qty>1</Qty>
   </Article>
  </Purchases>
    <Amount Currency="Dollars">2.54144E+2</Amount>
 </Statement>
</Data>

此转换有两个彼此嵌入的 SELECT。外部的 SELECT 处理发票列表。内部的 SELECT 处理项列表。与内部 SELECT 关联的 AS 子句期望一个数组:

            (SELECT II.Title                          AS Desc,
                                        CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost,
                               II.Quantity                     AS Qty
                          FROM I.Purchases.Item[] AS II
                          WHERE  II.UnitPrice > 0.0              ) 
                        -- Note the use of [] in the next expression
                              AS Purchases.Article[],

这告诉外部的 select 期望在每个结果中有可变数目的项。每个 SELECT 具有自己的相关名称:I 表示外部的 select,II 表示内部的 select。每个 SELECT 通常使用自己的相关名称,但内部 SELECT 的 FROM 子句引用外部 SELECT 的相关名称:

            (SELECT II.Title                          AS Desc,
                                        CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost,
                               II.Quantity                     AS Qty
                        -- Note the use of I.Purchases.Item in the next expression
                          FROM I.Purchases.Item[] AS II
             WHERE II.UnitPrice > 0.0 ) AS Purchases.Article[],

这告诉内部的 SELECT 处理当前发票项。两个 SELECT 都包含 WHERE 子句。外部 SELECT 使用一个标准废弃特定客户,内部的 SELECT 使用不同的标准废弃特定项。 此示例还显示 COALESCE 的使用以阻止缺少的输入字段导致缺少相关输出字段。最后,它还使用列函数 SUM 一起将所有项的值添加到每个发票中。列函数在引用数据库中的列中讨论。

当创建字段 Desc 时,复制整个输入标题字段:XML 属性和字段值。 如果您希望在输出消息中没有这些属性,则可以使用 FIELDVALUE 函数来丢弃它们,如下列 ESQL 的示例代码:

SET OutputRoot.XML.Data.Statement[] =
        (SELECT I.Customer.Title                                     AS Customer.Title, 
                        I.Customer.FirstName || ' ' || I.Customer.LastName   AS Customer.Name,
                        COALESCE(I.Customer.PhoneHome,'')                    AS Customer.Phone,
                        (SELECT FIELDVALUE(II.Title)                       AS Desc,
                                        CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost,
                               II.Quantity                     AS Qty
                          FROM I.Purchases.Item[] AS II
                          WHERE  II.UnitPrice > 0.0              )        AS Purchases.Article[],
                        (SELECT SUM( CAST(II.UnitPrice AS FLOAT) *
                                    CAST(II.Quantity AS FLOAT) *
                                    1.6                          )
                          FROM I.Purchases.Item[] AS II      )                AS Amount,
                   'Dollars'                                   AS Amount.(XML.Attribute)Currency
                    
                FROM InputRoot.XML.Invoice[] AS I
                WHERE I.Customer.LastName <> 'Brown'
    );

这生成以下输出消息:

<Data>
 <Statement>
  <Customer>
   		 		 <Title>Mr</Title>
   <Name>Andrew Smith</Name>
   <Phone>01962818000</Phone>
  </Customer>
  <Purchases>
   <Article>
    <Desc>The XML Companion</Desc>
    <Cost>4.472E+1</Cost>
    <Qty>2</Qty>
   </Article>
   <Article>
        <Desc>A Complete Guide to DB2 Universal Database</Desc>
    <Cost>6.872E+1</Cost>
    <Qty>1</Qty>
   </Article>
   <Article>
    <Desc>JAVA 2 Developers Handbook</Desc>
    <Cost>9.5984E+1</Cost>
    <Qty>1</Qty>
   </Article>
  </Purchases>
    <Amount Currency="Dollars">2.54144E+2</Amount>
 </Statement>
</Data>
相关概念
消息流概述
ESQL 概述
消息建模
相关任务
设计消息流
定义消息流内容
管理 ESQL 文件
处理大型 XML 消息
相关参考
Compute 节点
Database 节点
Filter 节点
ESQL 引用
CAST 函数
COALESCE 函数
SELECT 函数
SET 语句
示例消息
声明 | 商标 | 下载 | 书库 | 支持 | 反馈
Copyright IBM Corporation 1999, 2006 最后一次更新时间:2006/08/14
ak05750_