Oracle XMLQUERY: How to find an XML node to remove based on sub-node properties?

Question

Can you help me make the below code more robust and avoid hardcoding, please?

I have the following piece of code:

SET SERVEROUTPUT ON SIZE UNLIMITED;
exec DBMS_OUTPUT.ENABLE (NULL);
set lines 1000
set define OFF
DECLARE
  l_xml   VARCHAR2(32767);
BEGIN
         l_XML:='<?xml version="1.0"?>              ';
  l_XML:=l_XML||'<ROWSET>                           ';
  l_XML:=l_XML||' <ROW>                             ';
  l_XML:=l_XML||'  <TABLE_T>                        ';
  l_XML:=l_XML||'   <VERS_MAJOR>2</VERS_MAJOR>      ';
  l_XML:=l_XML||'   <CON0_LIST>                     ';
  l_XML:=l_XML||'    <CON0_LIST_ITEM>               ';
  l_XML:=l_XML||'     <NAME>FAC2TS</NAME>           ';
  l_XML:=l_XML||'     <CONTYPE>7</CONTYPE>          ';
  l_XML:=l_XML||'    </CON0_LIST_ITEM>              ';
  l_XML:=l_XML||'   </CON0_LIST>                    ';
  l_XML:=l_XML||'   <PROPERTY1>536870944</PROPERTY1>';
  l_XML:=l_XML||'   <CON2_LIST>                     ';
  l_XML:=l_XML||'    <CON2_LIST_ITEM>               ';
  l_XML:=l_XML||'     <NAME>FACTS_PK</NAME>         ';
  l_XML:=l_XML||'     <CONTYPE>3</CONTYPE>          ';
  l_XML:=l_XML||'    </CON2_LIST_ITEM>              ';
  l_XML:=l_XML||'   </CON2_LIST>                    ';
  l_XML:=l_XML||'   <PROPERTY>536870944</PROPERTY>  ';
  l_XML:=l_XML||'   <CON1_LIST>                     ';
  l_XML:=l_XML||'    <CON1_LIST_ITEM>               ';
  l_XML:=l_XML||'     <NAME>FACTS_PK</NAME>         ';
  l_XML:=l_XML||'     <CONTYPE>2</CONTYPE>          ';
  l_XML:=l_XML||'    </CON1_LIST_ITEM>              ';
  l_XML:=l_XML||'   </CON1_LIST>                    ';
  l_XML:=l_XML||'   <REFPAR_LEVEL>0</REFPAR_LEVEL>  ';
  l_XML:=l_XML||'  </TABLE_T>                       ';
  l_XML:=l_XML||' </ROW>                            ';
  l_XML:=l_XML||'</ROWSET>                          ';
   
   SELECT  XMLSERIALIZE( CONTENT 
                         XMLQUERY( 'copy $i := $p modify delete nodes $i/ROWSET[1]/ROW[1]/TABLE_T[1]/CON1_LIST[1] return $i'
                                   PASSING XMLTYPE(l_XML) AS "p" 
                                   RETURNING CONTENT
                                 ) 
                         INDENT SIZE=2
                       )
      INTO l_xml
   FROM dual;

   DBMS_OUTPUT.PUT_Line(l_XML);
END;

The /ROWSET[1]/ROW[1]/TABLE_T[1]/CON1_LIST[1] is hardcoded value to remove. In reality I need to remove a <CON*_LIST_ITEM> node (* is some number) that contains <CONTYPE>2</CONTYPE>.

Can you help with XMLQUERY that does that job?

Answer 1

You can filter all child nodes' names by regular expression and their child node CONTYPE: //*[fn:matches(name(),"^CON\D+_LIST$") and ./*/CONTYPE="2"]

Full example:

DECLARE
  l_xml   VARCHAR2(32767);
BEGIN
         l_XML:='<?xml version="1.0"?>              ';
  l_XML:=l_XML||'<ROWSET>                           ';
  l_XML:=l_XML||' <ROW>                             ';
  l_XML:=l_XML||'  <TABLE_T>                        ';
  l_XML:=l_XML||'   <VERS_MAJOR>2</VERS_MAJOR>      ';
  l_XML:=l_XML||'   <CON0_LIST>                     ';
  l_XML:=l_XML||'    <CON0_LIST_ITEM>               ';
  l_XML:=l_XML||'     <NAME>FAC2TS</NAME>           ';
  l_XML:=l_XML||'     <CONTYPE>7</CONTYPE>          ';
  l_XML:=l_XML||'    </CON0_LIST_ITEM>              ';
  l_XML:=l_XML||'   </CON0_LIST>                    ';
  l_XML:=l_XML||'   <PROPERTY1>536870944</PROPERTY1>';
  l_XML:=l_XML||'   <CON2_LIST>                     ';
  l_XML:=l_XML||'    <CON2_LIST_ITEM>               ';
  l_XML:=l_XML||'     <NAME>FACTS_PK</NAME>         ';
  l_XML:=l_XML||'     <CONTYPE>3</CONTYPE>          ';
  l_XML:=l_XML||'    </CON2_LIST_ITEM>              ';
  l_XML:=l_XML||'   </CON2_LIST>                    ';
  l_XML:=l_XML||'   <PROPERTY>536870944</PROPERTY>  ';
  l_XML:=l_XML||'   <CON1_LIST>                     ';
  l_XML:=l_XML||'    <CON1_LIST_ITEM>               ';
  l_XML:=l_XML||'     <NAME>FACTS_PK</NAME>         ';
  l_XML:=l_XML||'     <CONTYPE>2</CONTYPE>          ';
  l_XML:=l_XML||'    </CON1_LIST_ITEM>              ';
  l_XML:=l_XML||'   </CON1_LIST>                    ';
  l_XML:=l_XML||'   <REFPAR_LEVEL>0</REFPAR_LEVEL>  ';
  l_XML:=l_XML||'  </TABLE_T>                       ';
  l_XML:=l_XML||' </ROW>                            ';
  l_XML:=l_XML||'</ROWSET>                          ';
   
   SELECT 
      XMLSERIALIZE( 
         CONTENT 
             XMLQUERY( 
                 'copy $i := $p 
                  modify 
                  delete nodes 
                     $i//*[fn:matches(name(),"^CON\D+_LIST$") and ./*/CONTYPE="2"]
                  return $i'
                 PASSING XMLTYPE(l_XML) AS "p" 
                 RETURNING CONTENT
             ) 
             INDENT SIZE=2
           )
      INTO l_xml
   FROM dual;

   DBMS_OUTPUT.PUT_Line(l_XML);
END;
/