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('xyuv ') 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
------------------------------------------
uv
uv
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('xyuv ') 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
-------------------------------------------------------------------------------------------
uvuv
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
😎
