Review the following examples and modify them for your own use. They are all based on the Invoice message as input.
Consider the following ESQL:
SET OutputRoot.XML.Data.Output[] = (SELECT R.Quantity, R.Author FROM InputRoot.XML.Invoice.Purchases.Item[] AS R);
When the Invoice message is processed by this ESQL, the following output message is produced:
<Data> <Output> <Quantity>2</Quantity> <Autho>Neil Bradley</Autho> </Output> <Output> <Quantity>1</Quantity> <Autho>Don Chamberlin</Autho> </Output> <Output> <Quantity>1</Quantity> <Autho>Philip Heller, Simon Roberts</Autho> </Output> </Data>
There are three Output fields, one for each Item field. This is because, by default, SELECT creates an item in its result list for each item described by its FROM list. Within each Output field, there is a Field for each field named in the SELECT clause and these are in the order in which they are specified within the SELECT, not in the order in which they appear in the incoming message.
The R introduced by the final AS keyword is known as a correlation name. It is a local variable that represents in turn each of the fields addressed by the FROM clause. There is no significance to the name chosen. In summary, this simple transform does two things:
Here is the same transform implemented by a procedural algorithm:
DECLARE i INTEGER 1; DECLARE count INTEGER CARDINALITY(InputRoot.XML.Invoice.Purchases.Item[]); WHILE (i <= count) SET OutputRoot.XML.Data.Output[i].Quantity = InputRoot.XML.Invoice.Purchases.Item[i].Quantity; SET OutputRoot.XML.Data.Output[i].Author = InputRoot.XML.Invoice.Purchases.Item[i].Author; SET i = i+1; END WHILE;
These examples show that the SELECT version of the transform is much more concise. It also executes faster.
The following example shows a more advanced transformation:
SET OutputRoot.XML.Data.Output[] = (SELECT R.Quantity AS Book.Quantity, R.Author AS Book.Author FROM InputRoot.XML.Invoice.Purchases.Item[] AS R );
In this transform, there is an AS clause associated with each item in the SELECT clause. This gives each field in the result an explicit name rather than the field names being inherited from the input. These names can be paths (that is, a dot separated list of names), as shown in the example. Thus, the output message's structure can be arbitrarily different from the input message's. Using the same Invoice message, the result is:
<Data> <Output> <Book> <Quantity>2</Quantity> <Author>Neil Bradley</Author> </Book> </Output> </Data> <Data> <Output> <Book> <Quantity>2</Quantity> <Author>Neil Bradley</Author> </Book> </Output> <Output> <Book> <Quantity>1</Quantity> <Author>Don Chamberlin</Author> </Book> </Output> <Output> <Book> <Quantity>1</Quantity> <Author>Philip Heller, Simon Roberts</Author> </Book> </Output> </Data>
The expressions in the SELECT clause can be of any complexity and there are no special restrictions. They can include operators, functions, literals, and they can refer to variables or to fields not related to the correlation name. The following example shows more complex expressions:
SET OutputRoot.XML.Data.Output[] = (SELECT 'Start' AS Header, 'Number of books:' || R.Quantity AS Book.Quantity, R.Author || ':Name and Surname' AS Book.Author, 'End' AS Trailer FROM InputRoot.XML.Invoice.Purchases.Item[] AS R );
Using the same Invoice message, the result in this case is:
<Data> <Output> <Header>Start</Header> <Book> <Quantity>Number of books:2</Quantity> <Author>Neil Bradley:Name and Surname</Author> </Book> <Trailer>End</Trailer> </Output> <Output> <Header>Start</Header> <Book> <Quantity>Number of books:1</Quantity> <Author>Don Chamberlin:Name and Surname</Author> </Book> <Trailer>End</Trailer> </Output> <Output> <Header>Start</Header> <Book> <Quantity>Number of books:1</Quantity> <Author>Philip Heller, Simon Roberts:Name and Surname</Author> </Book> <Trailer>End</Trailer> </Output> </Data>
As shown above, the AS clauses of the SELECT clause contain a path that describes the full name of the field to be created in the result. These paths can also specify (as is normal for paths) the type of field to be created. The following example transform specifies the field types. In this case, XML tagged data is transformed to XML attributes:
SET OutputRoot.XML.Data.Output[] = (SELECT R.Quantity.* AS Book.(XML.Attribute)Quantity, R.Author.* AS Book.(XML.Attribute)Author FROM InputRoot.XML.Invoice.Purchases.Item[] AS R );
Using the same Invoice message, the result is:
<Data> <Output> <Book Quantity="2" Author="Neil Bradley"/> </Output> <Output> <Book Quantity="1" Author="Don Chamberlin"/> </Output> <Output> <Book Quantity="1" Author="Philip Heller, Simon Roberts"/> </Output> </Data>
Finally, you can use a WHERE clause to eliminate some of the results. In the following example a WHERE clause is used to remove results in which a specific criterion is met. An entire result is either included or excluded:
SET OutputRoot.XML.Data.Output[] = (SELECT R.Quantity AS Book.Quantity, R.Author AS Book.Author FROM InputRoot.XML.Invoice.Purchases.Item[] AS R WHERE R.Quantity = 2 );
Using the same input message, the result is:
<Data> <Output> <Book> <Quantity>2</Quantity> <Author>Neil Bradley</Author> </Book> </Output> </Data>