Oracle PL/SQL Tutorial/XML/extractvalue

Demo EXTRACTVALUE use in the WHERE clause

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>

Extract can be used in WHERE clause to search xml document

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>

Extract returns a COLLECTION of values (a repeating node)

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>