The FROM clause is not restricted to having one item. Specifying multiple items in the FROM clause produces the usual Cartesian product joining effect, in which there is an item in the result for all combinations of items in the two lists. This is the same joining effect as standard SQL.
The Invoice message includes a set of customer details, payment details, and details of the purchases that the customer makes. If you code the following ESQL to process the input Invoice message:
SET OutputRoot.XML.Items.Item[] = (SELECT D.LastName, D.Billing, P.UnitPrice, P.Quantity FROM InputBody.Invoice.Customer[] AS D, InputBody.Invoice.Purchases.Item[] AS P);
the following output message is generated:
<Items> <Item> <LastName>Smith</LastName> <Billing> <Address>14 High Street</Address> <Address>Hursley Village</Address> <Address>Hampshire</Address> <PostCode>SO213JR</PostCode> </Billing> <UnitPrice>27.95</UnitPrice> <Quantity>2</Quantity> </Item> <Item> <LastName>Smith</LastName> <Billing> <Address>14 High Street</Address> <Address>Hursley Village</Address> <Address>Hampshire</Address> <PostCode>SO213JR</PostCode> </Billing> <UnitPrice>42.95</UnitPrice> <Quantity>1</Quantity> </Item> <Item> <LastName>Smith</LastName> <Billing> <Address>14 High Street</Address> <Address>Hursley Village</Address> <Address>Hampshire</Address> <PostCode>SO213JR</PostCode> </Billing> <UnitPrice>59.99</UnitPrice> <Quantity>1</Quantity> </Item> </Items>
There are three results, giving the number of descriptions in the first list (one) multiplied by the number of prices in the second (three). The results systematically work through all the combinations of the two lists. You can see this by looking at the LastName and UnitPrice fields selected from each result:
LastName Smith UnitPrice 27.95 LastName Smith UnitPrice 42.95 LastName Smith UnitPrice 59.99
You can join data that occurs in a list and a non-list, or in two non-lists, and so on. For example:
OutputRoot.XML.Test.Result1[] = (SELECT ... FROM InputBody.Test.A[], InputBody.Test.b); OutputRoot.XML.Test.Result1 = (SELECT ... FROM InputBody.Test.A, InputBody.Test.b);
Note the location of the [] in each case. Any number of items can be specified in the FROM list, not just one or two. If any of the items specify [] to indicate a list of items, the SELECT generates a list of results (the list might contain only one item, but the SELECT can potentially return a list of items). The target of the assignment must specify a list (so must end in [] or you must use the THE keyword if you know that the WHERE clause guarantees that only one combination is matched.