в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:55:29Z

1 версия

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

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

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

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

в 13:45, 26 мая 2010

Admin: 1 версия