в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:06:02Z

1 версия

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

== Demo EXTRACTVALUE use in the WHERE clause==



<source lang="sql">
SQL> create table myTable
2 (ddl_id number primary key,
3 ddl xmltype )
4 /
Table created.
SQL>
SQL>
SQL> -- Populate the table with test data:
SQL> insert into myTable values (1,xmltype( (SELECT DBMS_METADATA.GET_XML("TABLE", "CUSTOMER", "STUDENT1") FROM DUAL) ) )
2 /

SQL>
SQL> insert into myTable values (2,xmltype.createxml( (SELECT DBMS_METADATA.GET_XML("TABLE", "ORD", "STUDENT1") FROM DUAL) ) )
2 /
SQL>
SQL>
SQL>
SQL> select extractvalue(ddl, "/ROWSET/ROW/TABLE_T/TS_NAME") AS tspace_name, t.block_size
2 from myTable d, user_tablespaces t
3 where extractvalue(ddl, "/ROWSET/ROW/TABLE_T/TS_NAME") = t.tablespace_name;
no rows selected
SQL>
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL></source>

== Extract can be used in WHERE clause to search xml document==



<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>
SQL>
SQL> select doc
2 from myTable
3 where extract(doc, "/message/greeting/text()")
4 like "%it may concern%"
5 /
no rows selected
SQL>
SQL> drop table myTable;
Table dropped.
SQL></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>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия