Oracle PL/SQL/XML/UPDATEXML

Reference one element

    
SQL> create table myTable of xmltype;
Table created.
SQL>
SQL> insert into myTable values(XMLTYPE("
  2    <customer>
  3     <name>AAA</name>
  4      <telephone>123 555-1234</telephone>
  5    </customer>"))
  6  /
1 row created.
SQL>
SQL> select * from myTable;

SYS_NC_ROWINFO$
------------------------------------------------------
  <customer>
   <name>AAA</name>
    <telephone>123 555-1234</telephone>
  </cu
1 row selected.
SQL>
SQL> update myTable c
  2  set value(c) = updateXML(value(c), "/customer/telephone/text()","888 555-1234")
  3  where existsnode(value(c),"/customer/name = "Chris Smith"") = 1
  4  /
0 rows updated.
SQL>
SQL> drop table myTable;
Table dropped.

UPDATEXML function supports multiple elements

  
SQL> CREATE TABLE myTable
  2  (id  NUMBER PRIMARY KEY
  3  ,doc XMLType NOT NULL)
  4  XMLTYPE doc STORE AS CLOB
  5  /
Table created.
SQL>
SQL>
SQL> 
SQL> update myTable
  2  set doc = updateXml( doc,
  3      "message/greeting/text()","test",
  4      "message/greeting/body/text()","test again"
  5  )
  6  where id = 1;
0 rows updated.
SQL> drop table myTable;
Table dropped.

Update xmltype type column

  
SQL> create table myTable of xmltype;
Table created.
SQL>
SQL> insert into myTable values(XMLTYPE("
  2    <customer>
  3     <name>name value</name>
  4    </customer>"))
  5  /
1 row created.
SQL>
SQL> select * from myTable;

SYS_NC_ROWINFO$
------------------------------------------------------
  <customer>
   <name>name value</name>
  </customer>
1 row selected.
SQL>
SQL> update myTable c
  2  set value(c) = updateXML(value(c), "/customer/name/text()","new value")
  3  /
1 row updated.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Use the UPDATEXML function to change element value

  
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>

Use UPDATEXML function to update a portion of the doc

  
SQL>
SQL>
SQL> CREATE TABLE myTable
  2  (id  NUMBER PRIMARY KEY
  3  ,doc XMLType NOT NULL)
  4  XMLTYPE doc STORE AS CLOB
  5  /
Table created.
SQL>
SQL>
SQL> update myTable
  2  set doc = updateXml(doc, "message/greeting/text()", "test")
  3  where id = 1;
0 rows updated.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.