<PART>part_0
   <SUBPART>subpart_1</SUBPART>
   <SUBPART>subpart_2</SUBPART>
   <SUBPART>subpart_3</SUBPART>
   <SUBPART>subpart_4</SUBPART>
   <SUBPART>subpart_5</SUBPART>
      
....
</PART>
<PART>part_1
   <SUBPART>subpart_1</SUBPART>
   <SUBPART>subpart_2</SUBPART>
   <SUBPART>subpart_3</SUBPART>
   <SUBPART>subpart_4</SUBPART>
   <SUBPART>subpart_5</SUBPART>
...
</PART>

with t as (
select xmlquery(' for $i in fn:collection("oradb:/HR/SOURCE_TABLE")
return $i ' returning content) as ddl_xml from dual)
select xmlquery('
for $p in /ROW
for $s in /ROW
where $p/PART=$s/PART
order by $p/PART, $p/SUBPART
return  $p/PART|$s/SUBPART' passing t.ddl_xml returning content) from t;

create table SOURCE_TABLE as select 'part_'||floor(level/10) as part, 'subpart_'||mod(level,10) as subpart from dual connect by level<=50;