From Oracle DB Extract the Value from CDATA based on Key

Question

My XML in the table as below

<fields><field key="2" val="5" type="D"/><![CDATA[<field key="3" val="SkpKSko=" 
type="T"></field>]]></fields>

I would like to get the value of SkpKSko= by passing key as 3

I was using the query

SELECT x.val  from Valuefromglobal ,xmltable('/fields/field[@key=2]' PASSING 
XMLFILED COLUMNS val VARCHAR2(400) PATH '@val' ) x 

The above query gives me the result of 5 the expected answer. However, if I change my argument to @key=3 to fetch CDATA value which results 0 records.

Answer 1

Perhaps you could get the desired result if you extract the CDATA using XMLTABLE, and then pass it to the next XMLTABLE to get the VAL attribute.

select
  x2.val
from xmltable('/fields'
              passing xmltype('
                        <fields>
                          <field key="2" val="5" type="D"/>
                          <![CDATA[<field key="3" val="SkpKSko=" type="T">
                                   </field>]]>
                        </fields>
                      ')
              columns cdata varchar2(2048) path 'text()') as x1,
     xmltable('/field[@key=3]'
              passing xmltype(x1.cdata)
              columns val varchar2(30) path '@val') as x2;

Output:

VAL
--------
SkpKSko=

DB<>FIDDLE