DELETEXML

Purpose

The DELETEXML function deletes one or more nodes in an XML document that match an XPath expression.

Syntax

DELETEXML( XMLType_instance, XPath_string [, namespace_string ])

Parameters

Return type

Returns the XMLType data type.

Examples

• Use the DELETEXML function to delete matching nodes from the target XML document. In this example, the target XML document is the <bookstore> node and its subnodes. The XPath expression is /bookstore/author, which indicates the <author> node under the <bookstore> node. The query result is renamed to result 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 result is as follows:

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

• After deleting all subnodes of a parent node, the parent node's namespace is still retained.

  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 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