Copying repeating fields
You can configure a node with ESQL to copy repeating fields in several ways.
About this task
Consider an input XML message that contains a repeating structure:
...
<Field_top>
<field1></field1>
<field1></field1>
<field1></field1>
<field1></field1>
<field1></field1>
</Field_top>
.....
You cannot copy this whole structure field with the following statement:
SET OutputRoot.XMLNS.Output_top.Outfield1 = InputRoot.XMLNS.Field_top.field1;
That statement copies only the first repeat, and therefore produces the same result as this statement:
SET OutputRoot.XMLNS.Output_top.Outfield1[1] = InputRoot.XMLNS.Field_top.field1[1];
You can copy the fields within a loop, controlling the iterations with the CARDINALITY of the input field:
SET I = 1;
SET J = CARDINALITY(InputRoot.XMLNS.Field_top.field1[]);
WHILE I <= J DO
SET OutputRoot.XMLNS.Output_top.Outfield1[I] = InputRoot.XMLNS.Field_top.field1[I];
SET I = I + 1;
END WHILE;
This might be appropriate if you want to modify each field in the output message as you copy it from the input field (for example, add a number to it, or fold its contents to uppercase), or after it has been copied. If the output message already contained more Field1 fields than existed in the input message, the surplus fields would not be modified by the loop and would remain in the output message.
The following single statement copies the iterations of the input fields to the output fields, and deletes any surplus fields in the output message.
SET OutputRoot.XMLNS.Output_top.Outfield1.[] = InputRoot.XMLNS.Field_top.field1[];
The
following example shows how you can rename the elements when you copy
them into the output tree. This statement does not copy across the
source element name, therefore each field1
element
becomes a Target
element.
SET OutputRoot.XMLNS.Output_top.Outfield1.Target[] =
(SELECT I FROM InputRoot.XMLNS.Field_top.field1[] AS I );
The next example shows a different way to do the same operation; it produces the same end result.
SET OutputRoot.XMLNS.Output_top.Outfield2.Target[]
= InputRoot.XMLNS.Field_top.field1[];
The
following example copies across the source element name. Each field1
element
is retained as a field1
element under the Target
element.
SET OutputRoot.XMLNS.Output_top.Outfield3.Target.[]
= InputRoot.XMLNS.Field_top.field1[];
This
example is an alternative way to achieve the same result, with field1
elements
created under the Target
element.
SET OutputRoot.XMLNS.Output_top.Outfield4.Target.*[]
= InputRoot.XMLNS.Field_top.field1[];
These examples show that there are several ways in which you can code ESQL to copy repeating fields from source to target. Select the most appropriate method to achieve the results that you require.
The principals shown here apply equally to all areas of the message tree to which you can write data, not just the output message tree.