Database Software

Hi ,

I have to extract values from below xml which includes multiple namepspaces. only one namespace  has value for xmlns. I managed to get node value for event tag where as info tag returns null value.

experrts help needed.

<?xml version="1.0" encoding="utf-8"?>

<event xmlns='http://www.w3.org/2001/XMLSchema-instance' >

<data xmlns=''>

<object>

<eventtype>110</eventtype>

<result>RESULT</result>

</object></data>

<info xmlns=''><jref>JREF</jref>

</info>

</event>

SELECT

  TMX.eventtype,

  TMX.result,

  TMX.jref

from EXAMPLE_MESSAGES1,

     XMLTABLE(XMLNAMESPACES(DEFAULT  'http://www.w3.org/2001/XMLSchema-instance'),

'for $i in /event/data/object

return element local:r{$i/eventtype,

                       $i/result,

                       $i/jref}'

PASSING EXAMPLE_MESSAGES1.XML_MESSAGE

COLUMNS

  EVENTTYPE VARCHAR2(30) PATH 'eventtype',

  RESULT VARCHAR2(30) PATH 'result',

  JREF VARCHAR2(30) PATH 'jref') TMX;

Database version is 11.2.0.3.0