可以使用LAST_VALUE在子元素之前获取最近的父元素（按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

为了回答附属问题“如果父元素和子元素中的内部标记名称相同怎么办”：

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'
;

作为一个框架挑战，不要像那样格式化XML。相反，将子对象嵌套在父对象中：

<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>

然后您可以使用：用途：

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;

其输出：
| 行数|parentId| PARENTNAME|行数|儿童|儿童姓名|
| --|--|--|--|--|--|
| 1 |H1|集管1| 1 |D1|详图D1|
| 1 |H1|集管1| 2 |D2|详图D2|
| 2 |H2|集流管2| 1 |D3|详图D3|
fiddle
理论上，使用您的格式，您将获得前面的myParent兄弟，并可以使用查询：

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;

但是，Oracle似乎不支持preceding-sibling::*语法，因此parent*列都作为NULL返回，不幸的是，查询不起作用。
fiddle