Oracle - XMLTABLE PATH get node ancestor

Question

I have XML that looks like:

<root>
    <uid>789</uid>
    <element>
      <uid>123</uid>
         <sub>
             <text>XYZ</text>
         </sub>
     </element>
</root>

The only constant thing is <text> node and the fact that <uid> node could be 2 level up. Rest of nodes could have any name so I cannot use fully qualified paths.

Based of <text> node I need to find <uid> node which is the nearest or for simplicity 2 levels up in the tree.

I tried:

WITH cte("XML") AS (
  SELECT '<root>
             <uid>789</uid>
           <element>
            <uid>123</uid>
            <sub>
                <text>XYZ</text>
            </sub>
            </element>
         </root>'
  FROM dual
)
SELECT x.*, c.*
FROM cte c,XMLTable('//text'
          PASSING XMLTYPE(c."XML")
          COLUMNS
           text VARCHAR2(4000) PATH '.'
            --,guid VARCHAR2(40) PATH '../../uid'  -- unsupported XQuery expression
            --,guid VARCHAR2(40) PATH 'ancestor::node()[2]/uid'  
             -- unsupported XQuery expression\
                 ) x  
WHERE text IS NOT NULL;

db<>fiddle demo

I am looking for solution similar to SQL Server:

WITH cte("XML") AS (
  SELECT CAST('<root>
             <uid>789</uid>
           <element>
            <uid>123</uid>
            <sub>
                <text>XYZ</text>
            </sub>
            </element>
         </root>' AS XML)
)
SELECT x.value('../../uid[1]', 'VARCHAR(10)') AS uid
     ,s.x.value('.', 'VARCHAR(10)') AS "text"
FROM cte c
CROSS APPLY c."XML".nodes('//text') s(x)

db<>fiddle demo

Answer 1

You should use preceding - it will return all nodes except any ancestor.
Order of preceding collection is from the beginning to the end. If you do this preceding::uid or more general preceding::* result will be (789, 123).

Combining everything together:

WITH cte("XML") AS (
  SELECT '<root>
             <uid>789</uid>
           <element>
            <uid>123</uid>
            <sub>
                <text>XYZ</text>
            </sub>
            </element>
         </root>'
  FROM dual
)
SELECT x.*, c.*
FROM cte c,XMLTable('//text'
          PASSING XMLTYPE(c."XML")
          COLUMNS
           text VARCHAR2(4000) PATH '.'
           ,guid VARCHAR2(40) PATH '(preceding::uid)[last() -1]/data(.)'  -- 2 -levelup            
                 ) x  
WHERE text IS NOT NULL;