SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> create table test
  2  (
  3  tst_fld XMLTYPE
  4  )
  5  /

Table created.

SQL> insert into test
  2  select XMLELEMENT("TABLES",xmlagg(XMLELEMENT("TABLENAME",TABLE_NAME))) from
 all_tables where rownum < 5
  3  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test
  2  /

TST_FLD
--------------------------------------------------------------------------------
<TABLES><TABLENAME>TAB$</TABLENAME><TABLENAME>USER$</TABLENAME><TABLENAME>BOOTSTRAP$</TABLENAME><TAB
LENAME>UNDO$</TABLENAME></TABLES>

select extract(tst_fld,'TABLES/TABLENAME') from test

select * from tablea where fld1 in (<XML node values related to 'TABLENAME'>)

select * from 
tablea 
where 
fld1 in 
(<XML node values related to 'TABLENAME'>)

SQL> create table test (tst_fld XMLTYPE);

Table created.

SQL> insert into test
  2  select XMLELEMENT("TABLES",xmlagg(XMLELEMENT("TABLENAME",TABLE_NAME))) 
  3  from all_tables 
  4  where rownum < 5
  5  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;
TST_FLD
--------------------------------------------------------------------------------
<TABLES><TABLENAME>CON$</TABLENAME><TABLENAME>UNDO$</TABLENAME><TABLENAME>CDEF$<
/TABLENAME><TABLENAME>CCOL$</TABLENAME></TABLES>

1 row selected.

SQL> select extractvalue(value(a), '/TABLENAME') tn
  2  from test, table(xmlsequence(extract(tst_fld, '/TABLES/TABLENAME'))) a
  3  /
TN
--------------------------------------------------------------------------------
CON$
UNDO$
CDEF$
CCOL$

4 rows selected.