Select within UpdateXML

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
😎
Marco Gralike Written by: