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.

<![CDATA[]]><![CDATA[]]> is the sample where under root tag i may have more than one CDATA with different keys — Mahesh ij, Feb 21 '19 at 14:33
I don't think you can query into a CDATA element. See here maybe - https://stackoverflow.com/questions/568315/how-do-i-retrieve-element-text-inside-cdata-markup-via-xpath — OldProgrammer, Feb 21 '19 at 15:12

Andrei Odegov · Accepted Answer · 2019-02-22T14:30:12.333

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

From Oracle DB Extract the Value from CDATA based on Key

1 Answers1