Different behavior of xmltable in oracle 12c R1 vs Oracle 19c
Team,
We have below query to retrieve LOGON ID using xmltable. This is working fine in Oracle 12c R1 , however giving NULL as a result in Oracle 19c.
select * from
xmltable(
'//test/requestedID'
passing xmltype('<test><requestedID><logonID>sachin</logonID></requestedID><requestedID><logonID>lara</logonID></requestedID><requestedID><logonID>virat</logonID></requestedID></test>')
columns logon_id varchar2(1024) path '/logonID'
) x ;
This is returning 3 rows with NULL as a result in Oracle 19c where as it is giving the right result in Oracle 12c which is shown as below:
sachin
lara
virat
However, when i remove varchar2(1024) after logon_id after columns in oracle 19c , it gives same result. When i remove '/' from logonID or put two // with logonID in columns , it gives correct result. However, i want to understand why an original query is not working in Oracle 19c but working fine in oracle 12c.