Stack Exchange
How to use PL/SQL to filter XML string
I am new to PL/SQL programming and I'm trying to write my first program. 
In an Oracle database I have a table called 'ngg_basiscomponent' with a column called 'data' containing some XML string. Here an example of a record:
<kenm>
    <weergv>
        <tekst aardVp="LO" hgtCe="2500" tekst="27">
            <ripunt x="109007204" y="505639703"/>
        </tekst>
    </weergv>
    <gdm:codes>
        <gdm:code wrd="LKI|TYPE.HS"/>
        <gdm:code wrd="LKI|GROOTTE.-"/>
        <gdm:code wrd="LKI|SCHAAL.-"/>
    </gdm:codes>
</kenm>

From these XML strings I want to delete the part from <gdm:codes> until </gdm:codes>.
I managed to do this by using the following query:
update ngg_basiscomponent 
set data = substr(data, 1, instr(data, regexp_substr(data, '<gdm:codes>.+</gdm:codes>'))-1) 
   || substr(data, instr(data, regexp_substr(data, '<gdm:codes>.+</gdm:codes>'))
     + length(regexp_substr(data, '<gdm:codes>.+</gdm:codes>')))
 where regexp_substr(data, '<gdm:codes>.+</gdm:codes>') is not null;

This works, but it is an ugly way to achieve this. It would be nice of I can define a variable to store the following part:
regexp_substr(data, '<gdm:codes>.+</gdm:codes>')

which defines the part that has to be removed. I'm trying to figure out how to do this by using some PL/SQL code. How do I achieve the same result using PL/SQL? 

Maybe XMLPATCH or XMLTRANSFORM can make this easier

XMLPATCH will only delete the data in the node but not the node,

DELETEXML
is what you need here. See below demo:
--Created table
create table tb as
  SELECT
                    xmltype('<?xml version="1.0"?>
                   <kenm>
                    <weergv>
                        <tekst aardVp="LO" hgtCe="2500" tekst="27">
                            <ripunt x="109007204" y="505639703"/>
                        </tekst>
                    </weergv>
                       <gdm>
                        <gdm wrd="LKI|TYPE.HS"/>
                        <gdm wrd="LKI|GROOTTE.-"/>
                        <gdm wrd="LKI|SCHAAL.-"/>
                    </gdm>    
                    </kenm>') col                        
                FROM     dual

-----------
--Selecting

Select * from tb;

--Output:

<?xml version="1.0"?>
                   <kenm>
                    <weergv>
                        <tekst aardVp="LO" hgtCe="2500" tekst="27">
                            <ripunt x="109007204" y="505639703"/>
                        </tekst>
                    </weergv>
                       <gdm>
                        <gdm wrd="LKI|TYPE.HS"/>
                        <gdm wrd="LKI|GROOTTE.-"/>
                        <gdm wrd="LKI|SCHAAL.-"/>
                    </gdm>    
                    </kenm>

----------
--Deleting

Update tb set col = DELETEXML(col,'/kenm/gdm');

---------

Select * from tb;

Output:

<?xml version="1.0"?>
<kenm>
  <weergv>
    <tekst aardVp="LO" hgtCe="2500" tekst="27">
      <ripunt x="109007204" y="505639703"/>
    </tekst>
  </weergv>
</kenm>


Sadly DELETEXML is deprecated - the syntax of DELETEXML was simpler than XQuery expression

@WernfriedDomscheit Thanks for your reply however can you please share where its mentioned that it has been deprecated

@XING - it's marked as deprecated in the docs from 12cR1.

This is what makes XML so painful to work with. The syntax is complicated enough to begin with, but in every major release it seems everything gets deprecated and replaced with something even more complicated for no clear reason. I feel for the OP.

It could be like this one:
CREATE TABLE XML_DATA (x XMLTYPE);
INSERT INTO XML_DATA VALUES (XMLTYPE('<kenm>
    <weergv>
        <tekst aardVp="LO" hgtCe="2500" tekst="27">
            <ripunt x="109007204" y="505639703"/>
        </tekst>
    </weergv>
    <codes>
        <code wrd="LKI|TYPE.HS"/>
        <code wrd="LKI|GROOTTE.-"/>
        <code wrd="LKI|SCHAAL.-"/>
    </codes>
</kenm>'));

UPDATE XML_DATA SET x = 
XMLQUERY('copy $kenm := . modify delete node $kenm//codes return $kenm' 
PASSING x
RETURNING CONTENT);

SELECT t.x.getclobval() FROM XML_DATA t;

<kenm>
  <weergv>
    <tekst aardVp="LO" hgtCe="2500" tekst="27">
      <ripunt x="109007204" y="505639703"/>
    </tekst>
  </weergv>
</kenm>

Note, XMLTYPE('<kenm> ... <gdm:codes>
        <gdm:code wrd="LKI|TYPE.HS"/>
        <gdm:code wrd="LKI|GROOTTE.-"/>
        <gdm:code wrd="LKI|SCHAAL.-"/>
    </gdm:codes> ...')
does not work, I get an error LPX-00234: namespace prefix "gdm" is not declared. There might be several ways to solve this.
