Use the UPDATEXML function to change element value : UPDATEXML « XML « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. XML
3. UPDATEXML

SQL>
SQL> CREATE TABLE myTable(
  2      id  NUMBER PRIMARY KEY,
  3      emps XMLType NOT NULL
  4  );

Table created.

SQL>
SQL> INSERT INTO myTable VALUES
  2  (1, xmltype('<?xml version="1.0" standalone="no" ?>
  3  <emps>
  4      <emp>
  5          <home_address>address</home_address>
  6      </emp>
  7  </emps>')
  8  );

1 row created.

SQL>
SQL>
SQL> update myTable
  2  set emps = updatexml(emps, '/emps/emp/home_address/text()','New York')
  3  where id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL>
SQL>