Search code examples
oracle-databasexpathextractxmltype

How to extract leaf nodes from Oracle XMLTYPE

I want to extract only the leaf nodes from an XMLTYPE object in Oracle 10g

SELECT
    t.getStringVal() AS text
FROM
    TABLE( XMLSequence( 
        XMLTYPE(
            '<xml>
                <node>
                    <one>text</one>
                </node>
                <node>
                    <two>text</two>
                </node>
                <node>
                    <three>text</three>
                </node>
            </xml>'
        ).extract( '//*' ) 
    ) ) t

What should I use as the WHERE clause so this returns only these:

                    <one>text</one>
                    <two>text</two>
                    <three>text</three>

I've tried the following but they don't work:

WHERE t.existsNode( '//*' ) = 0
WHERE t.existsNode( '/.//*' ) = 0
WHERE t.existsNode( './/*' ) = 0

What am I missing?

Solution

  • Nevermind, I found it:

    WHERE
    t.existsNode( '/*//*' ) = 0