Demo EXTRACTVALUE use in the WHERE clause : extractvalue « XML « Oracle PL/SQL Tutorial

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

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>