Mega Code Archive

 

Categories / Oracle PLSQL / XML

 

Use the EXTRACT function and the following XPath

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.