Having trouble getting the expected output. Neither the right join nor the full outer join seem to work with chained xmltable queries.
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
with t (xml) as (
select
'
<a>
<b>
<c>1</c>
<c>2</c>
</b>
<d>
<e>100</e>
<e>200</e>
<e>300</e>
</d>
</a>
' from dual
)
select y.*, z.*
from t
cross join
xmltable (
'/a'
passing xmltype(xml)
columns
b xmltype path 'b',
d xmltype path 'd'
) x
cross join
xmltable (
'/b/c'
passing x.b
columns
c number path '.',
o for ordinality
) y
right outer join
-- full outer join
xmltable (
'/d/e'
passing x.d
columns
d number path '.',
o for ordinality
) z
on (z.o = y.o)
;
expected output:
Message was edited by: mNem