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>