ESQL-> How to use SELECT statement to retrieve value from input XMLNSC?

Question

I need to access a specific XMLNSC value based on its name. I have an input xmlnsc that looks something like this

<Message>
    <Tags>
        <Name>ev1</Name>
       <Length>004</Length>
       <Value>0050</Value>
    </Tags>
</Message>

I am trying to access <Value>element by using <Name> element. That is my statement however I think is not correct.

SET OutputRoot.JSON.Data.records[i].userId = THE (SELECT ITEM FIELDVALUE (InputRoot.XMLNSC.Message.Tags.Value)
            FROM InputRoot.XMLNSC.Message.Tags
            WHERE InputRoot.XMLNSC.Message.Tags.Name = 'ev1');

I'm wondering what the right way to accomplish this in ESQL would be.Thanks in advance!

Answer 1

I think I spotted your error. You need [] in the FROM clause. This may work (not tested):

SET OutputRoot.JSON.Data.records[i].userId = THE (
        SELECT ITEM FIELDVALUE (tag.Value)
        FROM InputRoot.XMLNSC.Message.Tags[] AS tag
        WHERE tag.Name = 'ev1'
    );

Answer 2

You only need THE and ITEM for simple types, like character, I suggest you this:

SET OutputRoot.JSON.Data.records[i].userId = THE(SELECT ITEM P.Value FROM InputRoot.XMLNSC.Message.Tags[] AS P 
            WHERE P.Name = 'ev1');

This returns, if exist, the value of 'Value', not a ROW. So check it.