XMLPATCH in PL/SQL?

Question

I need to remove certain part of the XML (table definition from DBMS_METADATA). Found XMLPATCH and made it work for very small, example XML below. But I'm facing two problems: one big and one small.

• The big problem is that the below query fills up all of instance's PGA (PGA_TARGET=8GB) if I use it on real, 25KB XML with a table definition.
• The small problem is that I use that in PL/SQL so native PL/SQL would be better than SELECT FROM DUAL;

Does anyone know how to optimize the below to properly remove /ROWSET[1]/ROW[1]/TABLE_T[1]/CON1_LIST[1] with minimum memory usage?

Does anyone know how to rewrite the below into PL/SQL code?

SELECT XMLPATCH ( XMLTYPE( '<?xml version="1.0"?>
<ROWSET><ROW><TABLE_T><VERS_MAJOR>2</VERS_MAJOR><MAXTRANS>0</MAXTRANS>
<CON1_LIST><CON1_LIST_ITEM><OWNER_NUM>115</OWNER_NUM></CON1_LIST_ITEM></CON1_LIST>
<BHIBOUNDVAL empty="blob"/><PHYPART_NUM>10</PHYPART_NUM></TABLE_T></ROW></ROWSET>')
                , XMLTYPE( '<?xml version="1.0"?>
<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd
 http://xmlns.oracle.com/xdb/xdiff.xsd"
 xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?>
    <xd:delete-node 
     xd:node-type="element" 
     xd:xpath="/ROWSET[1]/ROW[1]/TABLE_T[1]/CON1_LIST[1]"/>
</xd:xdiff>')
)
FROM DUAL;
```

Answer 1

This one should fix the bigger problem and also should be faster:

select
     XMLQuery('copy $i := $p modify
                  delete nodes $i/ROWSET[1]/ROW[1]/TABLE_T[1]/CON1_LIST[1]
               return $i'
               PASSING XMLTYPE( '<?xml version="1.0"?>
                        <ROWSET>
                           <ROW>
                              <TABLE_T>
                                 <VERS_MAJOR>2</VERS_MAJOR>
                                 <MAXTRANS>0</MAXTRANS>
                                 <CON1_LIST>
                                     <CON1_LIST_ITEM>
                                        <OWNER_NUM>115</OWNER_NUM>
                                     </CON1_LIST_ITEM>
                                 </CON1_LIST>
                                 <BHIBOUNDVAL empty="blob"/>
                                 <PHYPART_NUM>10</PHYPART_NUM>
                              </TABLE_T>
                           </ROW>
                        </ROWSET>') AS "p" 
         RETURNING CONTENT
         ) xres
from dual;

Unfortunately we still use SQL in PL/SQL to update it:

declare
   x xmltype:= XMLTYPE( '<?xml version="1.0"?>
<ROWSET><ROW><TABLE_T><VERS_MAJOR>2</VERS_MAJOR><MAXTRANS>0</MAXTRANS>
<CON1_LIST><CON1_LIST_ITEM><OWNER_NUM>115</OWNER_NUM></CON1_LIST_ITEM></CON1_LIST>
<BHIBOUNDVAL empty="blob"/><PHYPART_NUM>10</PHYPART_NUM></TABLE_T></ROW></ROWSET>');
   res xmltype;
   g_doc dbms_xmldom.DOMDocument; -- basic DOM-document
   g_node dbms_xmldom.DOMNode;
 
begin
   select
      XMLQuery('copy $i := $p modify
                  delete nodes $i/ROWSET[1]/ROW[1]/TABLE_T[1]/CON1_LIST[1]
               return $i'
               PASSING x AS "p" 
         RETURNING CONTENT
         )
      into res
   from dual;
   dbms_output.put_line(res.getclobval());
end;
/

Answer 2

DELETEXML is also an answer:

WITH base
AS ( SELECT '<?xml version="1.0"?><ROWSET><ROW>
  <TABLE_T><VERS_MAJOR>2</VERS_MAJOR><MAXTRANS>0</MAXTRANS>
  "<CON1_LIST><CON1_LIST_ITEM><OWNER_NUM>115</OWNER_NUM></CON1_LIST_ITEM></CON1_LIST>"
  <BHIBOUNDVAL empty="blob"/><PHYPART_NUM>10</PHYPART_NUM></TABLE_T></ROW></ROWSET>' xml_data
  FROM dual 
   )
SELECT base.xml_data
     , XMLSERIALIZE( CONTENT DELETEXML(XMLTYPE(base.xml_data), '/ROWSET[1]/ROW[1]/TABLE_T[1]/CON1_LIST[1]') INDENT SIZE=2) xres
FROM base
;

...but it is deprecated, so Sayan's solution is better.