в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:06:01Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/XML/UPDATEXML - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия