Use the EXTRACT function and the following XPath:/emps/emp/interests/interest/

   
SQL>
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          <interests>
  5              <interest>i1</interest>
  6              <interest>i2</interest>
  7              <interest>i3</interest>
  8          </interests>
  9      </emp>
 10  </emps>')
 11  );

1 row created.

SQL>
SQL>
SQL> select extract(emps,'/emps/emp/interests/interest/text()')
  2  from myTable;



EXTRACT(EMPS,'/EMPS/EMP/INTERESTS/INTEREST/TEXT()')
------------------------------------------------------
i1i2i3

1 row selected.

SQL>
SQL> drop table myTable;

Table dropped.

Related examples in the same category

1.	Use extract function and xpath function to retrieve xml element data
2.	Use extract function to get xmltype data
3.	XPath text() function
4.	Oracle support XPath predicate expressions
5.	extract(doc, '/message/greeting/text()')
6.	Extract returns a COLLECTION of values (a repeating node)
7.	Find rows that contain the ROWSET/ROW node

Use the EXTRACT function and the following XPath:/emps/emp/interests/interest/ : xpath « XML « Oracle PL / SQL

Related examples in the same category