You can use SELECT statements that interact with both message data and databases. You can also nest a SELECT that interacts with one type of data within a SELECT that interacts with the other type.
Consider the following input message, which contains invoice information for two customers:
<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>
Consider the following database tables Prices and Addresses and their contents:
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
If you code the following ESQL transform:
-- 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 );
the following output message is generated. The input message is augmented with the price and address information from the database table:
<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>
You can nest the database SELECT within the message SELECT statement. In most cases this is not as efficient as the previous example, but you might find that it is better if the messages are small and the database tables are large.
-- 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 );