with qx as (
select * from xmltable('
for $i in //* return <x
pnode="{$i/../name()}"
pocc= "{$i/../@occ}"
node= "{$i/name()}"
occ= "{$i/@occ}"
val= "{$i/text()}"
></x>'
passing xmltype('
<myxml>
<n100>txt</n100>
<n200><![CDATA[bin1]]></n200>
<n300>txt0</n300>
<n400><![CDATA[bin2]]></n400>
<n500 occ="1">
<n600>
<n700>txt1</n700>
</n600>
</n500>
<n500 occ="2">
<n600>
<n700 occ="1">txt2</n700>
<n700 occ="2">txt3</n700>
</n600>
</n500>
</myxml>
')
columns
parent_node varchar2(100) path './@pnode',
parent_occ number(10) path './@pocc',
line for ordinality,
node varchar2(100) path './@node',
occ number(10) path './@occ',
val varchar2(2000) path './@val'
))
--select * from qx
,qp as (
select (
select max(line)
from qx q2
where q2.node=qx.parent_node
and q2.line<qx.line
) parent_line,
qx.*
from qx
)
--select * from qp
,qc as (
select
level lvl,
substr(sys_connect_by_path(node,'.'),2) nodepath,
qp.*
from qp
start with node='myxml'
connect by prior line = parent_line
order siblings by line
)
select * from qc
|