Accessing elements in the message body

Follow the relevant parent and child relationships from the top of the tree downwards, until you reach the required element.

• If you are referring to the input message tree to interrogate its content in a Compute node, use correlation name InputBody followed by the path to the element to which you are referring. InputBody is equivalent to InputRoot followed by the parser name (for example, InputRoot.MRM), which you can use if you prefer.
• If you are referring to the output message tree to set or modify its content in the Compute node, use correlation name OutputRoot followed by the parser name (for example, OutputRoot.MRM).
• If you are referring to the input message to interrogate its contents in a Database or Filter node, use correlation name Body to refer to the start of the message. Body is equivalent to Root followed by the parser name (for example, Root.XMLNS), which you can use if you prefer. You cannot use the Body correlation name in a DatabaseInput node.

  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:

Querying null values

When you compare an element to the ESQL keyword NULL, this tests whether the element is present in the logical tree that has been created from the input message by the parser.

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.

Setting null values

You can use two statements to set null values:

1. If you set the element to NULL by using the following statement, the element is deleted from the message tree:

   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.

2. If you set the value of this element to NULL as follows:

   SET OutputRoot.XMLNS.Invoice.Customer.Title VALUE = NULL;  

   the element is not deleted from the message tree. Instead, a special value of NULL is assigned to the element.

   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.