ESQL SELECT function return value filtered using attribute value as a condition

Question

I am trying to return the value of a element in an example message based on attribute value of the parent

the structure of the XML message is:

<transaction>
    <operation>
        <column name="A">
            <item>Desired Value</item>
        </column>
        <column name="B">
            <item>Other Value</item>
        </column>
    </operation>
</transaction>

In XPath this would be done in with the following query:

/transaction/operation/column[@name='A']/item/test()

But In ESQL I can not get it to work. I'm thinking it should be something akin to:

SELECT
    C.column.item
FROM
    InputRoot.transaction.operation as C
WHERE
    C.column.(XML.Attribute)name = A

Answer 1

Try this one:

SELECT
    C.item
FROM
    InputRoot.XMLNSC.transaction.operation.column[] AS C
WHERE
    C.name = 'A'