DELETEXML

Purpose

DELETEXML() deletes one or more nodes that match the XPath expression from the target XML document.

Syntax

DELETEXML( XMLType_instance, XPath_string [, namespace_string ])

Parameters

Return type

The return type is XMLType.

Examples

• Call DELETEXML() to delete the matching nodes from the target XML document, which is <bookstore> and its child nodes. The XPath expression is /bookstore/author, indicating that the <author> node under the <bookstore> node is to be deleted. The query result is renamed as result by using the AS keyword. Finally, the result is retrieved from the dual table.

  SELECT DELETEXML(
          XMLtype('<bookstore>
                    <book att="old"></book>
                    <author>carrot</author>
                  </bookstore>'),
          '/bookstore/author'
        ) AS result
  FROM dual;
  

  The return result is as follows:

  +-----------------------------------------------+
  | RESULT                                        |
  +-----------------------------------------------+
  | <bookstore>
    <book att="old"/>
  </bookstore>
  |
  +-----------------------------------------------+
  1 row in set
  

• Delete all child nodes and retain the namespace of the parent node.

  SELECT
    DELETEXML(
      xmltype(
        '<a xmlns="ns1" xmlns:f="ns2">
          <f:b b1="b1" b2="b2">bbb1</f:b>
          <b b1="b1" b2="b2">bbb2</b>
        </a>'
      ),
      '/a/*',
      'xmlns="ns1"'
    )
  FROM dual;
  

  The return result is as follows:

  +----------------------------------------------------------------------------------------------------------------------------------+
  | DELETEXML(XMLTYPE('<AXMLNS="NS1"XMLNS:F="NS2"><F:BB1="B1"B2="B2">BBB1</F:B><BB1="B1"B2="B2">BBB2</B></A>'),'/A/*','XMLNS="NS1"') |
  +----------------------------------------------------------------------------------------------------------------------------------+
  | <a xmlns="ns1" xmlns:f="ns2"/>
                                                                                                    |
  +----------------------------------------------------------------------------------------------------------------------------------+
  1 row in set