XMLType.getStringVal : XMLType « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. XMLType

SQL>
SQL> CREATE TABLE myTable (
  2     fall_id NUMBER,
  3     fall SYS.XMLType
  4  );

Table created.

SQL>
SQL> INSERT INTO myTable VALUES (1, SYS.XMLType.CreateXML(
  2     '<?xml version="1.0"?>
  3      <fall>
  4         <name>myTable</name>
  5         <county>USA</county>
  6         <state>MI</state>
  7         <url>
  8            http://your.com
  9         </url>
 10      </fall>'));

1 row created.


SQL>
SQL> CREATE INDEX by_name
  2     ON myTable f (
  3        SUBSTR(
  4           SYS.XMLType.getStringVal(
  5              SYS.XMLType.extract(f.fall,'/fall/name/text()')
  6           ),1,80
  7        )
  8     );

Index created.

SQL>
SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL>