XML Database (MOSC)

MOSC Banner

Different behavior of xmltable in oracle 12c R1 vs Oracle 19c

edited Jan 23, 2023 8:15AM in XML Database (MOSC) 1 commentAnswered

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center