Oracle Delete more than one node in XMLTYPE

Question

I have a XMLTYPE content and I want to remove more than one tag in that XML.

I'm using XMLQUERY to remove the tag recordType inside the Header, like this:

   SELECT XMLQUERY (
             'copy $i := $p modify
                  delete nodes $i//Header/recordType
                return $i'
             PASSING pv_header AS "p" RETURNING CONTENT)
     INTO result
     FROM DUAL;

What if I want to remove more than one tag without creating a new SELECT statement using XMLQUERY. I can't do this:

SELECT XMLQUERY (
                 'copy $i := $p modify
                      delete nodes $i//Header/recordType
                      delete nodes $i//Header/interfaceType
                    return $i'
                 PASSING pv_header AS "p" RETURNING CONTENT)
         INTO result
         FROM DUAL;

Note: I could use deleteXML but it is declared as a deprecated function in Oracle 12c.

Thanks in advance! Filipe

EDIT: I'm using this as reference: https://docs.oracle.com/database/121/ADXDB/xdb04cre.htm#ADXDB6086

Answer 1

You can have a sequence of calls to delete nodes, enclosed in parentheses for grouping, and separated by commas for sequence concatenation:

WITH t (pv_header) AS (
  SELECT xmltype('<Header><recordType/><interfaceType/><other/></Header>')
  FROM dual
)
SELECT XMLQUERY (
  'copy $i := $p modify
    (delete nodes $i//Header/recordType,
      delete nodes $i//Header/interfaceType)
    return $i'
  PASSING pv_header AS "p" RETURNING CONTENT)
FROM t;

XMLQUERY('COPY$I:=$PMODIFY(DELETENODES$I//HEADER/RECORDTYPE,DELETENODES$I//HEADE
--------------------------------------------------------------------------------
<Header><other/></Header>