Oracle PL/SQL/XML/UPDATEXML

Reference one element

   <source lang="sql">
   

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.

 </source>
   
  

UPDATEXML function supports multiple elements

   <source lang="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> 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.

 </source>
   
  

Update xmltype type column

   <source lang="sql">
 

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>

 </source>
   
  

Use the UPDATEXML function to change element value

   <source lang="sql">
 

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>

 </source>
   
  

Use UPDATEXML function to update a portion of the doc

   <source lang="sql">
 

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.

 </source>