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>