Oracle PL/SQL Tutorial/XML/UPDATEXML

Материал из SQL эксперт
Перейти к: навигация, поиск

Update (i.e. replace) the entire XMLType column with new content

   <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> update myTable 

 2  set doc = xmltype("<?xml version="1.0" standalone="yes"?>
 3  <message type="1">
 4    <greeting>Hi</greeting>
 5    <body>body!</body>
 6  </message>
 7  ");

0 rows updated. SQL> 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>

Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/XML/UPDATEXML&oldid=3341»