如何用Oracle XMLTable()关联XML中前序特定父节点？

问题背景 #

现有一份XML数据，父子关系由元素在XML中的位置决定：子元素属于前一个父元素，直到遇到下一个父元素。希望通过XMLTable()实现关联查询，将父元素与对应的子元素匹配输出。

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

期望输出 #

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

当前困境 #

已能分别通过XMLTable()提取父、子元素为独立表，但因XML无显性关联键，无法直接连接两者。现有代码如下：

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;

解决方案 #

核心思路是利用**元素的出现顺序（序号）**建立关联：

1. 给每个父元素、子元素都生成对应的出现序号（for ordinality）
2. 对每个子元素，找到序号小于它且最大的那个父元素，即为其所属的父元素

实现SQL如下：

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
),
parents as (
  select row_num as parent_row, parentID, parentName
  from xml,
       xmltable('myData/myParent' passing xml.data
                columns row_num for ordinality,
                        parentID varchar2(10) path 'parentID',
                        parentName varchar2(50) path 'parentName'
               )
),
children as (
  select row_num as child_row, childID, childName
  from xml,
       xmltable('myData/myChild' passing xml.data
                columns row_num for ordinality,
                        childID varchar2(10) path 'childID',
                        childName varchar2(50) path 'childName'
               )
)
select p.parentID, c.childID, c.childName
from children c
join parents p on p.parent_row = (
  select max(parent_row)
  from parents
  where parent_row < c.child_row
)
order by p.parentID, c.childID;

说明 #

• for ordinality用于生成元素在XML中的出现顺序序号，这是关联的核心依据
• 子查询(select max(parent_row) from parents where parent_row < c.child_row)负责找到每个子元素对应的最近的父元素
• 最终通过JOIN将父、子元素匹配，得到期望的关联结果

内容的提问来源于stack exchange，提问作者Fat P