How do I get just the value of this is a test for update RHMiscData table from CDATA in the following XML?
<p>
I've tried the following using REPLACE but need a cleaner solution for getting the values within CDATA:
</p>
SELECT t.COLUMN_VALUE.extract('/RH_MiscData/@DataName') .getStringVal() DataName,
REPLACE(REPLACE(t.COLUMN_VALUE.extract('/RH_MiscData/text()') .getStringVal(), '<![CDATA['),']]>') Data
FROM TABLE(xmlsequence(XMLTYPE('<Root>
<RH_MiscData DataName="Whats New Text">
<![CDATA[this is a test for update RHMiscData table]]>
</RH_MiscData>
</Root>') .extract('/Root/RH_MiscData'))) t