в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:55:30Z

1 версия

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

==extract(doc, "/message/greeting/text()")==



<source lang="sql">

SQL> CREATE TABLE myTable(
2 id NUMBER PRIMARY KEY
3 ,doc XMLType NOT NULL
4 )
5 XMLTYPE doc STORE AS CLOB
6 /
Table created.
SQL>
SQL> -- Extract with text() operator returns scalar text value:
SQL> select extract(doc, "/message/greeting/text()")
2 from myTable;
no rows selected
SQL>
SQL> drop table myTable;
Table dropped.

</source>

==Extract returns a COLLECTION of values (a repeating node)==



<source lang="sql">

SQL> CREATE TABLE myTable
2 (myID NUMBER PRIMARY KEY,
3 myValue XMLTYPE )
4 XMLTYPE myValue STORE AS CLOB
5 /
Table created.

SQL>
SQL>
SQL> select extract(myValue, "/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM/NAME")
2 from myTable
3 where extractvalue(myValue, "/ROWSET/ROW/TABLE_T/SCHEMA_OBJ/NAME") = "T";
no rows selected
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

</source>

==Find rows that contain the ROWSET/ROW node==



<source lang="sql">

SQL> CREATE TABLE myTable
2 (myID NUMBER PRIMARY KEY,
3 myValue XMLTYPE )
4 XMLTYPE myValue STORE AS CLOB
5 /
Table created.

SQL>
SQL>
SQL>
SQL> select myID
2 from myTable
3 where existsnode(myValue, "/ROWSET/ROW") = 1
4 /
no rows selected
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

</source>

==Oracle support XPath predicate expressions==



<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,"message[greeting="Hi"]/body/text()", "test again")
3 /
0 rows updated.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

</source>

==Use extract function and xpath function to retrieve xml element data==



<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 (1, xmltype("<?xml version="1.0" standalone="no"
?>
2 <emps>
3 <emp>
4 <home_address>address 1</home_address>
5 </emp>
6 </emps>")
7 );
1 row created.
SQL>
SQL>
SQL> select extract(emps, "/emps/emp/home_address/text()" )
2 from myTable
3 /

EXTRACT(EMPS,"/EMPS/EMP/HOME_ADDRESS/TEXT()")
------------------------------------------------------
address 1
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

</source>

==Use extract function to get xmltype data==



<source lang="sql">

SQL> CREATE TABLE myTable(
2 id NUMBER PRIMARY KEY,
3 emps XMLType NOT NULL
4 );
Table created.
SQL>
SQL> INSERT INTO myTable VALUES (1, xmltype("<?xml version="1.0" standalone="no"
?>
2 <emps>
3 <emp>
4 <home_address>address 1</home_address>
5 </emp>
6 </emps>")
7 );
1 row created.
SQL>
SQL>
SQL> select extract(emps, "/emps/emp/home_address/text()" )
2 from myTable
3 /

EXTRACT(EMPS,"/EMPS/EMP/HOME_ADDRESS/TEXT()")
------------------------------------------------------
address 1
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
SQL>

</source>

==Use the EXTRACT function and the following XPath:/emps/emp/interests/interest/==



<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 (1, xmltype("<?xml version="1.0" standalone="no"
?>
2 <emps>
3 <emp>
4 <interests>
5 <interest>i1</interest>
6 <interest>i2</interest>
7 <interest>i3</interest>
8 </interests>
9 </emp>
10 </emps>")
11 );
1 row created.
SQL>
SQL>
SQL> select extract(emps,"/emps/emp/interests/interest/text()")
2 from myTable;

EXTRACT(EMPS,"/EMPS/EMP/INTERESTS/INTEREST/TEXT()")
------------------------------------------------------
i1i2i3
1 row selected.
SQL>
SQL> drop table myTable;
Table dropped.

</source>

==XPath text() function==



<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 (1, xmltype("<?xml version="1.0" standalone="no"
?>
2 <emps>
3 <emp>
4 <home_address>address 1</home_address>
5 </emp>
6 </emps>")
7 );
1 row created.
SQL>
SQL>
SQL> select extract(emps, "/emps/emp/home_address/text()" )
2 from myTable
3 /

EXTRACT(EMPS,"/EMPS/EMP/HOME_ADDRESS/TEXT()")
------------------------------------------------------
address 1
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

</source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия