Oracle XMLTable() to Correlate with Previous Node with Specific Tag?

I have XML data where parent-child relationship was maintained based on the position in them XML (the child elements belong to the previous parent until the next parent is seen). Is it possible to use XMLTable() to do this?

Consider the following sample XML

<myData>
   <myParent>
      <parentID>H1</parentID>
      <parentName>Header 1</parentName>
   </myParent>
   <myChild>
      <childID>D1</childID>
      <childName>Detail D1</childName>
   </myChild>
   <myChild>
      <childID>D2</childID>
      <childName>Detail D2</childName>
   </myChild>
   <myParent>
      <parentID>H2</parentID>
      <parentName>Header 2</parentName>
   </myParent>
   <myChild>
      <childID>D3</childID>
      <childName>Detail D3</childName>
   </myChild>
</myData>

I want is something like

PARENTID    CHILDID     CHILDNAME
----------- ----------- ----------------------
H1          D1          Detail D1
H1          D2          Detail D2
H2          D3          Detail D3

The most preferred way is to a select statement, though other methods (e.g., table function, ref cursor) are also helpful.

I can extract the parents and children as tables separately. However, due to the XML design, I can't find a key to join between them.

with xml as (
select xmltype(
'<myData>
   <myParent>
      <parentID>H1</parentID>
      <parentName>Header 1</parentName>
   </myParent>
   <myChild>
      <childID>D1</childID>
      <childName>Detail D1</childName>
   </myChild>
   <myChild>
      <childID>D2</childID>
      <childName>Detail D2</childName>
   </myChild>
   <myParent>
      <parentID>H2</parentID>
      <parentName>Header 2</parentName>
   </myParent>
   <myChild>
      <childID>D3</childID>
      <childName>Detail D3</childName>
   </myChild>
</myData>') data
from dual
)
select child.* from xml,
--XMLTable for header
--xmltable('myData/myParent' passing
--xml.data
--columns row_num for ordinality,
--         "parentID",
--         "parentName"
--) parent,
--XMLTable for detail
xmltable('myData/myChild' passing
xml.data
columns row_num for ordinality,
         "childID",
         "childName"
) child;

db<>fiddle

This outputs the only children as a table. (Commented out section is another XMLTable() to extract the parent. However, there is no way to join the two)

Solution

You can use LAST_VALUE to get the nearest parent before the children (by row_num) :

with xml as (
select xmltype(
'<myData>
   <myParent>
      <parentID>H1</parentID>
      <parentName>Header 1</parentName>
   </myParent>
   <myChild>
      <childID>D1</childID>
      <childName>Detail D1</childName>
   </myChild>
   <myChild>
      <childID>D2</childID>
      <childName>Detail D2</childName>
   </myChild>
   <myParent>
      <parentID>H2</parentID>
      <parentName>Header 2</parentName>
   </myParent>
   <myChild>
      <childID>D3</childID>
      <childName>Detail D3</childName>
   </myChild>
</myData>') data
from dual
)
, expanded AS (
    SELECT rn, childid, childname, parentid, parentname, 
        LAST_VALUE(parentid IGNORE NULLS)
            OVER(ORDER BY RN ) AS latestparentid
    FROM xml, 
    xmltable('myData/*' passing
        xml.data
        columns rn for ordinality,
        childid PATH 'childID' ,
        childname PATH 'childName',
        parentid PATH 'parentID',
        parentname PATH 'parentName'
    ) node
)
SELECT p.parentid, p.parentname, c.childid, c.childname
FROM expanded p 
JOIN expanded c ON p.parentid = c.latestparentid
WHERE p.parentid IS NOT NULL
;


H1  Header 1        
H1  Header 1    D1  Detail D1
H1  Header 1    D2  Detail D2
H2  Header 2        
H2  Header 2    D3  Detail D3