Home » Developer & Programmer » JDeveloper, Java & XML » Getting error while using ExtractValue function.
Getting error while using ExtractValue function. [message #636570] Tue, 28 April 2015 07:25 Go to next message
Asfakul
Messages: 43
Registered: July 2014
Member
Hi All, I was trying to use ExtractValue function to extract a node. But I am getting an error which I cant understand.

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>'));


Here is what I was trying.

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



Please let me know what's the correct way to use ExtractValue and why should we use it instead of XMLTable function.
Re: Getting error while using ExtractValue function. [message #636572 is a reply to message #636570] Tue, 28 April 2015 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66776
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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


You have 4 "employee" nodes, EXTRACTVALUE works on a single node so it can't work. It is like using TO_CHAR on an array of numbers.
You have to break the single XML into 4 XML one per employee like:
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.

And this is what does xmltable in an easy way:
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.

Re: Getting error while using ExtractValue function. [message #636576 is a reply to message #636572] Tue, 28 April 2015 08:16 Go to previous messageGo to next message
Asfakul
Messages: 43
Registered: July 2014
Member
Thanks Michel. So which one is preferable to use ? XMLTable or ExtractValue ,I am familiar with XMLTable but not much with ExtractValue and xmlsequence .
Re: Getting error while using ExtractValue function. [message #636577 is a reply to message #636576] Tue, 28 April 2015 08:45 Go to previous message
Michel Cadot
Messages: 66776
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is the same thing, choose the way you prefer.
Note that xmltable allows you to type the returned columns when extractvalue just return a big VARCHAR2.
In addition, xmltable has many other options and features, it is a wrapper for the other xml functions.

If you don't know xmlsequence and other xml functions, I advise you to first study xmltable.

Previous Topic: ORA-29542: class AccidentReport already defined by source AccidentReport
Next Topic: How to Generate XML Output From Oracle Database Using A Procedure
Goto Forum:
  


Current Time: Sun Jan 19 19:40:20 CST 2020