Xpath and Oracle XMLTABLE

Question

I have a CLOB containing an XML.

<attrs>
    <attr name="1">
        <string>stringvalue</string>
    </attr>
    <attr name="2">
        <integer>1</integer>
    </attr>
    <attr name="3">
        <integer>2</integer>
    </attr>
    <attr name="4">
        <boolean>false</boolean>
    </attr>
</attrs>

How can I get only boolean attributs and the name attribute ?

XMLTABLE('/attrs/attr/boolean'
    PASSING XMLTYPE(CLOB)
    COLUMNS ATTRIBUTENAME VARCHAR2(50) PATH '???',
            ATTRIBUTEVALUE VARCHAR2(5) PATH '.'
) X

This way, I only have the value, how can I also get the parent @name ? I can use '/attrs/attr' but I don't know how to get only the boolean (maybe I need to filter after ?)

Thanks!

Answer 1

You can walk back up the tree in the column path with:

'./../@name'

so that gives:

XMLTABLE('/attrs/attr/boolean'
    PASSING XMLTYPE(CLOB)
    COLUMNS ATTRIBUTENAME VARCHAR2(50) PATH './../@name',
            ATTRIBUTEVALUE VARCHAR2(5) PATH '.'
) X

which with your data gets:

ATTRIBUTENAME ATTRIBUTEVALUE
------------- --------------
4             false

You could also apply a child-node filter in the main XPath with:

'/attrs/attr[boolean]'

and then get that attr node's name and its child boolean node, giving:

XMLTABLE('/attrs/attr[boolean]'
    PASSING XMLTYPE(your_CLOB)
    COLUMNS ATTRIBUTENAME VARCHAR2(50) PATH '@name',
            ATTRIBUTEVALUE VARCHAR2(5) PATH 'boolean'
) X

which gets the same result.

db<>fiddle