WebSphere Message Brokers
File: ak05750_
Writer: Bill Oppenheimer

Task topic

This build: July 31, 2007 21:32:28

Transforming a complex XML message

When you code the ESQL for a Compute node, use the SELECT statement for complex message transformation.

Examples

Review the following examples and modify them for your own use. They are all based on the Invoice message as input:

In this example, Invoice contains a variable number of Items. The transform is shown below:

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' 
    );

The output message that is generated is:

<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>    

This transform has two SELECTs nested inside each other. The outer one operates on the list of Invoices. The inner one operates on the list of Items. The AS clause associated with the inner SELECT expects an array:

            (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[],

This tells the outer select to expect a variable number of Items in each result. Each SELECT has its own correlation name: I for the outer select and II for the inner one. Each SELECT typically uses its own correlation name, but the inner SELECT's FROM clause refers to the outer SELECT's correlation name:

            (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[],

This tells the inner SELECT to work with the current Invoice's Items. Both SELECTs contain WHERE clauses. The outer one uses one criterion to discard certain Customers and the inner one uses a different criterion to discard certain Items. The example also shows the use of COALESCE to prevent missing input fields causing the corresponding output field to be missing. Finally, it also uses the column function SUM to add together the value of all Items in each Invoice. Column functions are discussed in Referencing columns in a database.

When the fields Desc are created, the whole of the input Title field is copied: the XML attributes and the field value. If you do not want these attributes in the output message, you can use the FIELDVALUE function to discard them; for example code the following 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' 
    );

That generates the following output message:

<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>
Related concepts
Message flows overview
ESQL overview
Message modeling
Related tasks
Designing a message flow
Defining message flow content
Managing ESQL files
Handling large XML messages
Related reference
Compute node
Database node
Filter node
ESQL reference
CAST function
COALESCE function
SELECT function
SET statement
Example message
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2007Copyright IBM Corporation 1999, 2007. All Rights Reserved.
This build: July 31, 2007 21:32:28

ak05750_ This topic's URL is: