XMLTABLE extract element's posision()

Question

I have XML like:

<all>
    <cell>
        <data>A</data>
        <data>A1</data>
        <data>A2</data>
        <data>A3</data>
    </cell>
    <cell>
        <data>B</data>
        <data>C</data>
        <data>D</data>
        <data>E</data>
    </cell>
    <cell>
        <data>X</data>
        <data>X</data>
        <data>X</data>
        <data>X</data>
    </cell>
</all>

I want to extract in table content on every all/cell/data and unique path to the element. There aren't IDs in attributes or another unique mark and my idea for unique path is for example:

/all/cell[2]/data[5]

Expected result(example XML) is:

Content | Path
----------------------
A       |/all/cell[1]/data[1]
A1      |/all/cell[1]/data[2]
A2      |/all/cell[1]/data[3]
A3      |/all/cell[1]/data[4]
B       |/all/cell[2]/data[1]
C       |/all/cell[2]/data[2]
D       |/all/cell[2]/data[3]
E       |/all/cell[2]/data[4]
X       |/all/cell[3]/data[1]
X       |/all/cell[3]/data[2]
X       |/all/cell[3]/data[3]
X       |/all/cell[3]/data[4]

I tried using XMLTABLE to call posision() of elements but it returns only "1" for every element.

select b.*
  from dropme a,
       xmltable('/all/cell/data' passing
                xmltype(xml_text) columns 
                cmp_old varchar2(200) path 'text()', -- work corect!
                cmp_old1 varchar2(200) path '.', -- work corect! Result is same as cmp_old(upper filed)
                rowcnt number path 'position()', -- return only value "1"
                rowname varchar2(200) path 'name()', -- work corect! Return "data" as result
                parent_rowcnt number path '../position()', -- return only value "1"
                parent_rowname varchar2(200) path '../name()' -- work corect! Return "cell" as result
                ) b;

I can't understand why it works for functions name() and text() but there is problem with position().

I try to find problem with position() or another solution to extract path and content of all element.

Answer 1

Use multiple correlated XMLTABLE with FOR ORDINALITY instead of position():

select b.cell_no,
       b.cell_name,
       c.*
from   dropme a
       CROSS JOIN
       xmltable(
         '/all/cell'
         passing xmltype(xml_text)
         columns
           cell_no   FOR ORDINALITY,
           cell_name VARCHAR2(200) PATH 'name()',
           cell      XMLType       PATH '.'
       ) b
       CROSS JOIN
       XMLTABLE(
         '/cell/data'
         PASSING b.cell
         COLUMNS
           cmp_old varchar2(200) path 'text()',
           rowcnt FOR ORDINALITY,
           rowname varchar2(200) path 'name()'
       ) c;

CELL_NO | CELL_NAME | CMP_OLD | ROWCNT | ROWNAME
------: | :-------- | :------ | -----: | :------
      1 | cell      | A       |      1 | data   
      1 | cell      | A1      |      2 | data   
      1 | cell      | A2      |      3 | data   
      1 | cell      | A3      |      4 | data   
      2 | cell      | B       |      1 | data   
      2 | cell      | C       |      2 | data   
      2 | cell      | D       |      3 | data   
      2 | cell      | E       |      4 | data   
      3 | cell      | X       |      1 | data   
      3 | cell      | X       |      2 | data   
      3 | cell      | X       |      3 | data   
      3 | cell      | X       |      4 | data   

db<>fiddle here