Use extract function to get xmltype data : xpath « XML « Oracle PL / SQL






Use extract function to get xmltype data

   

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>

   
    
    
  








Related examples in the same category

1.Use extract function and xpath function to retrieve xml element 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