Selecting Unique XML Segments Based on an Element's Value and Map to Another xml in IBM ESQL

Question

I have an XML like

<Root>
    <Header/>
    <Ins>
       <X/>
       <Y/>
       </Z>
    </Ins>
</Root>

'Ins' segment can appear multiple times. The element 'X' may or may not have value; but the values are not unique as well. The element 'Y' always contains values. I need to consider those 'Ins' segments where values of 'X' are unique (no value should not consider as a unique value) and the values of 'Y' are unique and the values of 'Y' have substring 'something'. From these selected 'Ins' segments I want to map the value of the corresponding 'Z' element to the element 'C1' of another xml

<Target>
    <C>
      <C1/>
    </C>
</Target>

I have written the code so far as:

DECLARE i INTEGER 1;
DECLARE j INTEGER 1;
FOR source AS InputRoot.XMLNSC.Root.Ins[] DO
    SET Environment.SourceData.Folder[i].FieldX = coalesce(source.X, '');
    SET Environment.SourceData.Folder[i].FieldY = coalesce(source.Y, '');
    SET Environment.SourceData.Folder[i].FieldZ = coalesce(source.Z, '');
    IF (Environment.SourceData.Folder[i].FieldX <> '' AND CONTAINS(Environment.SourceData.Folder[i].FieldY, 'something')) THEN
        OutputRoot.XMLNSC.Target.C[j].C1 = Environment.SourceData.Folder[i].FieldZ;
        SET j = j + 1;
    END IF;
    SET i = i + 1;
END FOR;  

But I am unable to build the logic for unique 'X' values. I am using IBM ACE 12 and IBM ESQL.

Answer 1

You have two options:

a) write all the records into a temporary database table, then use SQL SELECT with the DISTINCT or GROUP BY keywords

or

b) write some custom code to implement your mapping rules.

If you choose option b) then I recommend that you do it one step at a time, and test carefully before moving to the next step:

1. Filter out records where X has no value
2. Filter out records with duplicate values of X and Y
3. Map the value of Z from the remaining records 4.