Delete xml node with a specific CDATA

This is part of my XML:

 <EO Name="oracle.apps.hcm.goals.core.publicModel.entity.GoalMeasurementEO">    
                    <![CDATA[0001FFFFFFFF]]>    
                    <GoalMeasurementEORow PS="0" Hdl="16" PK="Y" CI="Y" AV="111111111111111111111111111111">    
                       <MeasurementId>  
                          <DATA null="true"/>   
                       </MeasurementId> 
                    </GoalMeasurementEORow> 
                 </EO>  
    <EO Name="oracle.apps.hcm.goals.core.publicModel.entity.GoalMeasurementEO"> 
                    <![CDATA[00010000000EACED00057708000110D943311B2F]]>    
                    <GoalMeasurementEORow PS="0" Hdl="1019" PK="Y" CI="Y" AV="">    
                       <MeasurementId>  
                          <DATA>300000297949999</DATA>  
                       </MeasurementId> 
                       <BusinessGroupId>    
                          <DATA>1</DATA>    
                       </BusinessGroupId>   
                     </EO>

I need to delete the EO node with <![CDATA[0001FFFFFFFF]]>,across the table using Oracle. This xml is stored in XM_DATA_CACHE of XMLTYPE. Is there a way to delete based on CDATA

Final output should be

<EO Name="oracle.apps.hcm.goals.core.publicModel.entity.GoalMeasurementEO"> 
                    <![CDATA[0001FFFFFFFF]]>    
                    <GoalMeasurementEORow PS="0" Hdl="16" PK="Y" CI="Y" AV="111111111111111111111111111111">    
                       <MeasurementId>  
                          <DATA null="true"/>   
                       </MeasurementId> 
                    </GoalMeasurementEORow> 
                 </EO>  ```

Answer

First of all your xml is broken: second tag GoalMeasurementEORow is not closed and also since that is just a part of xml, it has no root tag.

Anyway, I’ve fixed your xml to show how you can remove cdata from your xml.

Please look at the following example:

-- sample data:
with t(x) as (
select xmltype(q'{
<ROOT>
<EO Name="oracle.apps.hcm.goals.core.publicModel.entity.GoalMeasurementEO">    
                    <![CDATA[0001FFFFFFFF]]>    
                    <GoalMeasurementEORow PS="0" Hdl="16" PK="Y" CI="Y" AV="111111111111111111111111111111">    
                       <MeasurementId>  
                          <DATA null="true"/>   
                       </MeasurementId> 
                    </GoalMeasurementEORow> 
                 </EO>  
    <EO Name="oracle.apps.hcm.goals.core.publicModel.entity.GoalMeasurementEO"> 
                    <![CDATA[00010000000EACED00057708000110D943311B2F]]>    
                    <GoalMeasurementEORow PS="0" Hdl="1019" PK="Y" CI="Y" AV="">    
                       <MeasurementId>  
                          <DATA>300000297949999</DATA>  
                       </MeasurementId> 
                       <BusinessGroupId>    
                          <DATA>1</DATA>    
                       </BusinessGroupId>
                       </GoalMeasurementEORow>
                     </EO>
</ROOT>
}')
from dual)-- end of sample data
-- main query:
select
  XMLQuery(
        'copy $i := $p1 modify
            (for $j in $i//EO
              let $o := <EO >{$j/@*}{ $j/node()[name() ne ""]}</EO>
                return replace node $j with $o
                )
        return $i' 
        PASSING t.x AS "p1"
        returning content
        ) xdata_new
from t;

Results:

XDATA_NEW
----------------------------------------------------------------------------------
<ROOT>
  <EO Name="oracle.apps.hcm.goals.core.publicModel.entity.GoalMeasurementEO">
    <GoalMeasurementEORow PS="0" Hdl="16" PK="Y" CI="Y" AV="111111111111111111111111111111">
      <MeasurementId>
        <DATA null="true"/>
      </MeasurementId>
    </GoalMeasurementEORow>
  </EO>
  <EO Name="oracle.apps.hcm.goals.core.publicModel.entity.GoalMeasurementEO">
    <GoalMeasurementEORow PS="0" Hdl="1019" PK="Y" CI="Y" AV="">
      <MeasurementId>
        <DATA>300000297949999</DATA>
      </MeasurementId>
      <BusinessGroupId>
        <DATA>1</DATA>
      </BusinessGroupId>
    </GoalMeasurementEORow>
  </EO>
</ROOT>

As you can see I’m replacing all child nodes EO with their content and CDATA disappears.