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

Question

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)

Answer 1

To answer the subsidiary question "what if the inner tag names are the same in parent and child elements":

with xml (data) as (
  select '<myData>
   <myParent>
      <ID>H1</ID>
      <Name>Header 1</Name>
   </myParent>
   <myChild>
      <ID>D1</ID>
      <Name>Detail D1</Name>
   </myChild>
   <myChild>
      <ID>D2</ID>
      <Name>Detail D2</Name>
   </myChild>
   <myParent>
      <ID>H2</ID>
      <Name>Header 2</Name>
   </myParent>
   <myChild>
      <ID>D3</ID>
      <Name>Detail D3</Name>
   </myChild>
</myData>' from dual
)
, expanded as (
    select x.rn, x.id, x.name, x.kind, 
        LAST_VALUE(case kind when 'myParent' then id end IGNORE NULLS)
            OVER(ORDER BY RN ) AS latestparentid
    from xml d
    cross join xmltable(
      '//myData/*'
      passing xmltype(d.data)
        columns 
            rn for ordinality,
            id PATH 'ID',
            name PATH 'Name',
            kind varchar2(20) PATH 'local-name()'
    ) x
)
SELECT p.id, p.name, c.id as childid, c.name as childname
FROM expanded p 
JOIN expanded c ON p.id = c.latestparentid AND c.kind = 'myChild'
WHERE p.kind = 'myParent'
;

Answer 2

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

Answer 3

As a frame challenge, don't format your XML like that. Instead, nest the children in the parent:

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

Then you can use:

select p.row_num,
       p.parentid,
       p.parentname,
       c.*
from   xml
       CROSS APPLY xmltable(
         '/myData/myParent'
         passing xml.data
         COLUMNS
           row_num for ordinality,
           parentId   VARCHAR2(10) PATH 'parentID',
           parentName VARCHAR2(50) PATH 'parentName',
           children   XMLTYPE      PATH 'children'
       ) p
       CROSS APPLY xmltable(
         '/children/myChild'
         passing p.children
         COLUMNS
           row_num for ordinality,
           childId    VARCHAR2(10) PATH 'childID',
           childName  VARCHAR2(50) PATH 'childName'
       ) c;

Which outputs:

ROW_NUM	PARENTID	PARENTNAME	ROW_NUM	CHILDID	CHILDNAME
1	H1	Header 1	1	D1	Detail D1
1	H1	Header 1	2	D2	Detail D2
2	H2	Header 2	1	D3	Detail D3

fiddle

---

Theoretically, with your format, you would get the preceding myParent sibling and could use the query:

select c.*
from   xml
       CROSS APPLY xmltable(
         '/myData/myChild'
         passing xml.data
         COLUMNS
           row_num for ordinality,
           parentId   VARCHAR2(10) PATH './preceding-sibling::myParent/parentID',
           parentName VARCHAR2(50) PATH './preceding-sibling::myParent/parentName',
           childId    VARCHAR2(10) PATH 'childID',
           childName  VARCHAR2(50) PATH 'childName'
       ) c;

However, Oracle does not appear to support the preceding-sibling::* syntax so the parent* columns are all returned as NULL and, unfortunately, the query does not work.

fiddle