Oracle PL/SQL Tutorial/XML/extractvalue
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>