Use Text() function to get text value from xml string : Introduction « XML « Oracle PL/SQL Tutorial

SQL> CREATE TABLE myTable(
  2      id  NUMBER PRIMARY KEY,
  3      emps XMLType NOT NULL
  4  );

Table created.

SQL>
SQL> INSERT INTO myTable VALUES (1, xmltype('<?xml version="1.0" standalone="no"
 ?>
  2  <emps>
  3      <emp>
  4          <home_address>address 1</home_address>
  5      </emp>
  6  </emps>')
  7  );

1 row created.

SQL>
SQL>
SQL> select extract(emps, '/emps/emp/home_address/text()' )
  2  from myTable
  3  /



EXTRACT(EMPS,'/EMPS/EMP/HOME_ADDRESS/TEXT()')
------------------------------------------------------
address 1

1 row selected.

SQL>
SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL>
SQL>

33.1.Introduction
	33.1.1.	SQL to XML
	33.1.2.	Create a table to hold the simple "Hello World" XML document
	33.1.3.	Create table based on single data type
	33.1.4.	Create table myTable of xmltype
	33.1.5.	Use Aggregrate functions on XML data
	33.1.6.	Use Text() function to get text value from xml string
	33.1.7.	xmlagg and xmlforest
	33.1.8.	Without the text() operator, returns node name + text value