Esql how to use select statement to retrieve value from input xml

Question

I have an input xml that looks something like this

<log>
    <line>
        <id>1</id>
    <line>
        <id>2</id>
    <otherLine>
        <id>2</id>
        <field>
            12345-67
        </field>
    </otherLine>
</log>

What I do in esql is basically iterate over every <line> element and I want retrieve the value from <otherLine> where the <id> values match.

I have code like this:

declare inDoc reference to InputRoot.XMLNSC.log;
declare line reference to inDoc;
declare fieldValue character;

move line firstchild name 'line';
while lastmove(line) do

    set fieldValue = select r.field from inDoc.otherLine[] as r where r.id = line.id;
    -- I want this to be null when hitting the line with <id>1</id> and 12345-67 and hitting the line with <id>2</id>

    move line nextsibling name 'line';
end while

I get the following error when I try to deploy the code:

BIP2497E: (MyApp_flow.Main, <line number of select statement>) : Illegal data type for target. A list field reference is required. 

The expression supplying the target must evaluate to a value of a suitable type. The given expression cannot possibly do so. 

Correct the syntax of your ESQL expression in node 'MyApp_flow.Main', around line and column '<line number of the select statement>', then redeploy the message flow.

I'm wondering what the right way to accomplish this in esql would be

Thanks

Answer 1

The solution is to use the,item, and fieldvalue in the select statement like this

set fieldValue = the(select item fieldvalue(r.field) from inDoc.otherLine[] as r where r.id = line.id);