Below is a simplified example and everything works perfectly with recursive CTE instead of connect by but I'm curious if there is any way to get it working with connect by without persisting intermediate result in a table.
Here is our input data.
create table x(x) as
select '
<root>
<flag><row><x>1</x></row></flag>
<row><x>2</x><xp>1</xp></row>
<row><x>3</x></row>
</root>' from dual;
The goal is to return x, xp and the name of a parent tag.
As I mentioned, everything works perfectly with recursive CTE.
SQL> with xt as
2 (
3 select x, xp, y
4 from xmltable('//row'
5 passing (select xmltype(x) from x)
6 columns
7 x varchar2(30) path 'x',
8 xp varchar2(30) path 'xp',
9 y varchar2(30) path 'name(..)'
10 )
11 ),
12 rec(lvl, x, xp, y) as
13 (
14 select 1, t.x, t.xp, t.y
15 from xt t where x = 1
16 union all
17 select lvl+1, t.x, t.xp, t.y
18 from xt t
19 join rec r on r.x = t.xp
20 )
21 select *
22 from rec;
LVL X XP Y
---------- ------------------------------ ------------------------------ ------------------------------
1 1 flag
2 2 1 root
Now let's try with connect by…
SQL> with xt as
2 (
3 select x, xp, y
4 from xmltable('//row'
5 passing (select xmltype(x) from x)
6 columns
7 x varchar2(30) path 'x',
8 xp varchar2(30) path 'xp',
9 y varchar2(30) path 'name(..)'
10 )
11 )
12 select level lvl, t.*
13 from xt t
14 start with x = 1
15 connect by prior x = xp;
LVL X XP Y
---------- ------------------------------ ------------------------------ ------------------------------
1 1
2 2 1
Oh… name is empty.
Let's try with non-mergeable view.
SQL> with xt as
2 (
3 select x, xp, y
4 from xmltable('//row'
5 passing (select xmltype(x) from x)
6 columns
7 x varchar2(30) path 'x',
8 xp varchar2(30) path 'xp',
9 y varchar2(30) path 'name(..)'
10 )
11 )
12 select level lvl, t.*
13 from
14 (select * from xt where rownum >= 1) t
15 start with x = 1
16 connect by prior x = xp;
LVL X XP Y
---------- ------------------------------ ------------------------------ ------------------------------
1 1
2 2 1
Same result.
Ok, let's try to “isolate” recordset returned by xmltable by using collect (or cast + multiset).
SQL> with xt as
2 (
3 select x, xp, y
4 from xmltable('//row'
5 passing (select xmltype(x) from x)
6 columns
7 x varchar2(30) path 'x',
8 xp varchar2(30) path 'xp',
9 y varchar2(30) path 'name(..)'
10 )
11 )
12 select level lvl, t.*
13 from
14 table(select collect(to_3x(x, xp, y)) from xt) t
15 --table(cast(multiset(select * from xt) as tt_3x))
16 start with x = 1
17 connect by prior x = xp;
LVL X XP N
---------- ---------- ---------- ------------------------------
1 1
2 2 1
No luck again.
Let's try to concat and split attributes.
SQL> with xt as
2 (
3 select x, xp, y
4 from xmltable('//row'
5 passing (select xmltype(x) from x)
6 columns
7 x varchar2(30) path 'x',
8 xp varchar2(30) path 'xp',
9 y varchar2(30) path 'name(..)'
10 )
11 ),
12 t as
13 (
14 select
15 to_number(regexp_substr(s, '[^#]+', 1, 1)) x,
16 nvl(regexp_substr(s, '[^#]+', 1, 2), '!!!') y,
17 to_number(regexp_substr(s, '[^#]+', 1, 3)) xp
18 from (select x||'#'||y||'#'||xp s from xt)
19 )
20 select *
21 from t
22 start with x = 1
23 connect by prior x = xp;
X Y XP
---------- -------------------------------------------------------------------------------------------- ----------
1 !!!
In this case it is even worse - we've got only one row.
I will not continue with all the details however there are some other options I tried…
materialize hint does not seem to work with xmltableno_query_transformation either has no effect or causes ORA-00600/ORA-03113- similarly
NO_XML_QUERY_REWRITE and other XML related hints did not help either
This problem occurs on any version I tried from 12 to 23 (26?).
It is a bit frustrating that nothing has been done to fix this in more than 15 years!
It's a shame Oracle is not open-source.
It would be much easier to find magical knob to tame XML transformations (or better fix them) with an access to source code.
So to get this working we can use either recursive CTE of CTAS with the result from xmltable + connect by.
The question is if we can get this working with xmltable + connect by without CTAS.
Is someone can get this working with xmlsequence/xmlquery that is also interesting but xmltable is a preference.
PS. Types used
create or replace type to_3x as object
(
x int,
xp int,
n varchar2(30)
)
/
create or replace type tt_3x as table of to_3x
/
PPS. Even cursor expressions fail to work with xmltable, but that is a separate story.
SQL> with xt as
2 (
3 select x, xp, y
4 from xmltable('//row'
5 passing (select xmltype(x) from x)
6 columns
7 x varchar2(30) path 'x',
8 xp varchar2(30) path 'xp',
9 y varchar2(30) path 'name(..)'
10 )
11 )
12 select cursor(select * from xt)
13 from dual;
with xt as
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-19103: VALUE keyword keyword