Extract can be used in WHERE clause to search xml document : extractvalue « XML « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. XML
3. extractvalue

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>