Oracle PL/SQL Tutorial/XML/UPDATEXML

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

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

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.


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.
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/XML/UPDATEXML&oldid=3341»