Oracle XMLTABLE - how to remove a node from XMLType?-CodePudding

Assume we have following XML:

<root>
  <item>
    <a>a1</a>
    <b>b1</b>
    <c>c1</c>
    <d>d1</d>
    <e>e1</e>
  </item>
  <item>
    <a>a2</a>
    <b>b2</b>
    <c>c2</c>
    <d>d2</d>
    <e>e2</e>
  </item>
  ...
</root>

How to get following result using XMLTABLE and PATH?

A   B   ITEM_XML (excluding <d>)
a1  b1  <item><a>a1</a><b>b1</b><c>c1</c><e>e1</e></item>
a2  b2  <item><a>a2</a><b>b2</b><c>c2</c><e>e2</e></item>

No DELETEXML please as it is deprecated. I am particularly interested how to remove/exclude some node from XMLType. Mind that ITEM_XML should be pretty printed like in original, it was just more convenient to put it in the table this way.

CodePudding user response：

Starting with Oracle Database 12c Release 1 (12.1.0.1) use XQuery Update to update XML data. (https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adxdb/deprecated-functions-for-updating-XML-data.html). Example of deleting node:

UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify delete node
        $tmp/Warehouse/VClearance return $tmp'
       PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;