CREATE TABLE EMPLOYEES
(
   id     NUMBER,
   data   XMLTYPE
);
INSERT INTO EMPLOYEES
     VALUES (1, xmltype ('<employees>
    <employee type="admin" emplid="1111">
        <firstname>John</firstname>
        <lastname>Watson</lastname>
        <age>30</age>
        <email>johnwatson@sh.com</email>
    </employee>
    <employee type="admin" emplid="2222">
        <firstname>Sherlock</firstname>
        <lastname>Homes</lastname>
        <age>32</age>
        <email>sherlock@sh.com</email>
    </employee>
    <employee type="user" emplid="3333">
        <firstname>Jim</firstname>
        <lastname>Moriarty</lastname>
        <age>52</age>
        <email>jim@sh.com</email>
    </employee>
    <employee type="user" emplid="4444">
        <firstname>Mycroft</firstname>
        <lastname>Holmes</lastname>
        <age>41</age>
        <email>mycroft@sh.com</email>
    </employee>
</employees>'));

select extractValue(e.data,'/employees/employee/firstname')
from employees e;

SQL> select extractValue(e.data,'/employees/employee/firstname')
  2  from employees e;
from employees e
     *
ERROR at line 2:
ORA-19025: EXTRACTVALUE returns value of only one node

SQL> select extractValue(x.column_value,'/employee/firstname') name
  2  from employees e,table(xmlsequence(extract(e.data,'/employees/employee'))) x
  3  /
NAME
----------------------------------------------------------------------------------
John
Sherlock
Jim
Mycroft

4 rows selected.

SQL> select nam
  2  from employees e,
  3       xmltable('/employees/employee' passing e.data
  4                columns nam varchar2(10) path '/employee/firstname')
  5  /
NAM
----------
John
Sherlock
Jim
Mycroft

4 rows selected.