ESQL : Extracting Unique Elements from Recurring XML Segment

Question

I am facing a challenge with ESQL code. I have an XML message that contains a recurring segment, and I need to extract unique values from a specific element within that segment. Following is the input XML message,

<MSG>
    <RecurringSegment>
        <Element>val1</Element>
    </RecurringSegment>
    <RecurringSegment>
        <Element>val2</Element>
    </RecurringSegment>
    <RecurringSegment>
        <Element>val1</Element>
    </RecurringSegment>
    <RecurringSegment>
        <Element>val3</Element>
    </RecurringSegment>
    <!-- ... (more recurring segments) ... -->
</MSG>

I have written the following ESQL code that iterates through the recurring segment, identifies unique values within the specific element (e.g., <Element>), and stores them in an output variable.

DECLARE uniqueVals ROW;
DECLARE i INTEGER 1;

FOR obj AS InputRoot.XMLNSC.MSG.RecurringSegment[] DO
    IF (NOT(CONTAINS(uniqueVals, obj.Element))) THEN
        SET uniqueVals.val[i] = obj.Element;
        SET i = i + 1;
    END IF;
END FOR;

But this is not working; need assistance with the ESQL code to achieve this.

Answer 1

The CONTAINS function works only on string data types (BIT,BLOB,CHAR), not on ROW.

Following works good in case you have not too many unique values:

DECLARE occurences ROW;
DECLARE i INTEGER 1;

FOR obj AS InputRoot.XMLNSC.MSG.RecurringSegment[] DO
    DECLARE val CHAR obj.Element;
    IF COALESCE(occurences.{val}, 0) = 0 THEN
        SET occurences.{val} = 1;
    ELSE
        SET occurences.{val} = occurences.{val} + 1;
    END IF;
END FOR;

FOR obj AS occurences.*[] DO
    IF FIELDVALUE(obj) > 1 THEN
        CREATE LASTCHILD OF OutputRoot.XMLNSC.Result
               NAME FIELDNAME(obj) VALUE FIELDVALUE(obj);
    END IF;
END FOR;

With your input this produces following result:

<Result>
  <val1>2</val1>
</Result>

This style of programming is not efficient for many unique values because occurences.{val} is not implemented by ESQL like a hash map.

Answer 2

The requirement is to find the unique values. Adjusting Daniel Steinmann's answer a little...

DECLARE uniqueValues ROW;

FOR obj AS InputRoot.XMLNSC.MSG.RecurringSegment[] DO
    DECLARE val CHAR obj.Element;
    IF ( EXISTS(uniqueValues .{val}[]) ) THEN
        CREATE LASTCHILD OF uniqueValues TYPE Name NAME val 
    END IF;
END FOR;

SET OutputRoot.XMLNSC.Result[] = uniqueValues.*[];

Not able to test this, so I apologise for any logic/syntax errors.