Use extract function and xpath function to retrieve xml element data : xpath « XML « Oracle PL / SQL






Use extract function and xpath function to retrieve xml element data

   
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          <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>

   
    
    
  








Related examples in the same category

1.Use extract function to get xmltype data
2.XPath text() function
3.Oracle support XPath predicate expressions
4.Use the EXTRACT function and the following XPath:/emps/emp/interests/interest/
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