Oracle PL/SQL/XML/xpath

extract(doc, "/message/greeting/text()")

   
SQL> CREATE TABLE myTable(
  2      id  NUMBER PRIMARY KEY
  3      ,doc XMLType NOT NULL
  4  )
  5  XMLTYPE doc STORE AS CLOB
  6  /
Table created.
SQL>
SQL> -- Extract with text() operator returns scalar text value:
SQL> select extract(doc, "/message/greeting/text()")
  2  from myTable;
no rows selected
SQL>
SQL> drop table myTable;
Table dropped.

Extract returns a COLLECTION of values (a repeating node)

    
SQL> CREATE TABLE myTable
  2    (myID            NUMBER PRIMARY KEY,
  3     myValue     XMLTYPE )
  4     XMLTYPE myValue STORE AS CLOB
  5  /
Table created.


SQL>
SQL> 
SQL> select extract(myValue, "/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM/NAME")
  2  from myTable
  3  where extractvalue(myValue, "/ROWSET/ROW/TABLE_T/SCHEMA_OBJ/NAME") = "T";
no rows selected
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Find rows that contain the ROWSET/ROW node

    
SQL> CREATE TABLE myTable
  2    (myID            NUMBER PRIMARY KEY,
  3     myValue     XMLTYPE )
  4     XMLTYPE myValue STORE AS CLOB
  5  /
Table created.

SQL>
SQL>
SQL> 
SQL> select myID
  2  from myTable
  3  where existsnode(myValue, "/ROWSET/ROW")  = 1
  4  /
no rows selected
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Oracle support XPath predicate expressions

   
SQL> CREATE TABLE myTable
  2  (id  NUMBER PRIMARY KEY
  3  ,doc XMLType NOT NULL)
  4  XMLTYPE doc STORE AS CLOB
  5  /
Table created.
SQL>
SQL>
SQL> 
SQL> update myTable
  2  set doc = updateXml( doc,"message[greeting="Hi"]/body/text()", "test again")
  3  /
0 rows updated.
SQL>
SQL> drop table myTable;
Table dropped.
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>

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>

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.

XPath text() function

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