I saw the following example on the XMLDB forum.
I can imagine that this can be applied for good use.
Thanks “michaels”.
SQL> SELECT * FROM v$version; BANNER ------------------------------------------------------------------ Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production PL/SQL Release 9.2.0.7.0 - Production CORE 9.2.0.7.0 Production TNS FOR Linux: Version 9.2.0.7.0 - Production NLSRTL Version 9.2.0.7.0 - Production SQL> WITH t AS (SELECT xmltype('<a><b>xy</b><c>uv</c></a>') x FROM dual) 2 -- 3 -- 4 SELECT updatexml(x,'a/b/text()', (SELECT t.x.extract('//c/text()') FROM t t)) result 5 FROM t; RESULT ------------------------------------------ <a> <b>uv</b> <c>uv</c> </a> SQL> SELECT * FROM v$version; BANNER -------------------------------------------------------------------------------- Oracle DATABASE 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS FOR 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production SQL> SET autotrace ON SQL> WITH t AS (SELECT xmltype('<a><b>xy</b><c>uv</c></a>') x FROM dual) 2 -- 3 -- 4 SELECT updatexml(x,'a/b/text()', (SELECT t.x.extract('//c/text()') FROM t t)) result 5 FROM t; RESULT ------------------------------------------------------------------------------------------- <a><b>uv</b><c>uv</c></a> Execution Plan ---------------------------------------------------------- Plan hash value: 1317351201 ----------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | | 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------- Statistics ---------------------------------------------------------- 384 recursive calls 0 db block gets 739 consistent gets 13 physical reads 0 redo size 1895 bytes sent via SQL*Net TO client 1003 bytes received via SQL*Net FROM client 7 SQL*Net roundtrips TO/FROM client 1 sorts (memory) 0 sorts (disk) 1 rows processed
