Copying repeating fields

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.