When you want to access the contents of a message, for reading or writing, use the structure and arrangement of the elements in the tree that is created by the parser from the input bit stream.
Follow the relevant parent and child relationships from the top of the tree downwards, until you reach the required element.
You must use these different correlation names because there is only one message to which to refer in a Database or Filter node; you cannot create an output message in these nodes. Use a Compute node to create an output message.
Assume that you have created a message flow that handles the message Invoice, shown in the figure in Writing ESQL. If, for example, you want to interrogate the element CardType from within a Compute node, use the following statement:
IF InputBody.Invoice.Payment.CardType='Visa' THEN
DO;
-- more ESQL --
END IF;
If you want to make the same test in a Database or Filter node (where the reference is to the single input message), code:
IF Body.Invoice.Payment.CardType='Visa' THEN
DO;
-- more ESQL --
END IF;
If you want to copy an element from an input XML message to an output message in the Compute node without changing it, use the following ESQL:
SET OutputRoot.XMLNS.Invoice.Customer.FirstName =
InputBody.Invoice.Customer.FirstName;
If you want to copy an element from an input XML message to an output message and update it, for example by folding to uppercase or by calculating a new value, code:
SET OutputRoot.XMLNS.Invoice.Customer.FirstName =
UPPER(InputBody.Invoice.Customer.FirstName);
SET OutputRoot.XMLNS.Invoice.InvoiceNo = InputBody.Invoice.InvoiceNo + 1000;
If you want to set a STRING element to a constant value, code:
SET OutputRoot.XMLNS.Invoice.Customer.Title = 'Mr';
You can also use the equivalent statement:
SET OutputRoot.XMLNS.Invoice.Customer.Title VALUE = 'Mr';
If you want to update an INTEGER or DECIMAL, for example the element TillNumber, with the value 26, use the following assignment (valid in the Compute node only):
SET OutputRoot.MRM.Invoice.TillNumber=26;
The integer data type stores numbers using the 64-bit twos complement form, allowing numbers in the range -9223372036854775808 to 9223372036854775807. You can specify hexadecimal notation for integers as well as normal integer literal format. The hexadecimal letters A to F can be written in uppercase or lowercase, as can the X after the initial zero, which is required. The following example produces the same result as the example shown earlier:
SET OutputRoot.MRM.Invoice.TillNumber= 0x1A;
The following examples show SET statements for element types that do not appear in the Example message.
To set a FLOAT element to a non-integer value, code:
SET OutputRoot.MRM.FloatElement1 = 1.2345e2;
To set a BINARY element to a constant value, code:
SET OutputRoot.MRM.BinaryElement1 = X'F1F1';
For BINARY values, you must use an initial character X (uppercase or lowercase) and enclose the hexadecimal characters (also uppercase or lowercase) in single quotation marks, as shown.
To set a BOOLEAN element to a constant value (the value 1 equates to true, 0 equates to false), code:
SET OutputRoot.MRM.BooleanElement1 = true;
or
SET OutputRoot.MRM.BooleanElement1 = 1;
You can use the SELECT statement to filter records from an input message without reformatting the records, and without any knowledge of the complete format of each record. Consider the following example:
-- Declare local variable
DECLARE CurrentCustomer CHAR 'Smith';
-- Loop through the input message
SET OutputRoot.XMLNS.Invoice[] =
(SELECT I FROM InputRoot.XMLNS.Invoice[] AS I
WHERE I.Customer.LastName = CurrentCustomer
);
This code writes all records from the input message to the output message if the WHERE condition (LastName = Smith) is met. All records that do not meet the condition are not copied from input message to output message. I is used as an alias for the correlation name InputRoot.XMLNS.Invoice[].
The declared variable CurrentCustomer is initialized on the DECLARE statement: this option is the most efficient way of declaring a variable for which the initial value is known.
You can use this alias technique with other SELECT constructs. For example, if you want to select all the records of the input message, and create an additional record:
-- Loop through the input message
SET OutputRoot.XMLNS.Invoice[] =
(SELECT I, 'Customer' || I.Customer.LastName AS ExtraField
FROM InputRoot.XMLNS.Invoice[] AS I
);
You could also include an AS clause to place records in a subfolder in the message tree:
-- Loop through the input message
SET OutputRoot.XMLNS.Invoice[] =
(SELECT I AS Order
FROM InputRoot.XMLNS.Invoice[] AS I
);
If you are querying or setting elements that contain, or might contain, null values, be aware of the following considerations:
For example, you can check whether an invoice number is included in the current invoice message with the following statement:
IF InputRoot.XMLNS.Invoice.InvoiceNo IS NULL THEN
DO;
-- more ESQL --
END IF;
You can also use an ESQL reference, as shown in the following example:
DECLARE cursor REFERENCE TO InputRoot.MRM.InvoiceNo;
IF LASTMOVE(cursor) = FALSE THEN
SET OutputRoot.MRM.Analysis = 'InvoiceNo does not exist in logical tree';
ELSEIF FIELDVALUE(cursor) IS NULL THEN
SET OutputRoot.MRM.Analysis =
'InvoiceNo does exist in logical tree but is defined as an MRM NULL value';
ELSE
SET OutputRoot.MRM.Analysis = 'InvoiceNo does exist and has a value';
END IF;
For more information about declaring and using references, see Creating dynamic field references. For a description of the LASTMOVE and FIELDVALUE functions, see LASTMOVE function and FIELDTYPE function.
If the message is in the MRM domain, there are additional considerations for querying null elements that depend on the physical format. For further details, see Querying null values in a message in the MRM domain.
SET OutputRoot.XMLNS.Invoice.Customer.Title = NULL;
If the message is in the MRM domain, there are additional considerations for null values that depend on the physical format. For further details, see Setting null values in a message in the MRM domain.
This technique is called implicit null processing.
SET OutputRoot.XMLNS.Invoice.Customer.Title VALUE = NULL;
SET OutputRoot.XMLNS.Invoice.Customer.Title = NULL;
If the message is in the MRM domain, the content of the output bit stream depends on the settings of the physical format null handling properties. For further details, see Setting null values in a message in the MRM domain.
This technique is called explicit null processing.
If you set an MRM complex element or an XML, XMLNS, or JMS parent element to NULL without using the VALUE keyword, that element and all its children are deleted from the logical tree.