ORA-19025: EXTRACTVALUE returns value of only one node

(Doc ID 1436776.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

The following sample code illustrates EXTRACTVALUE working for a single value.

Create the following table and insert a single record. This record has only 1 employee (Ename) under the EmployeeList.

CREATE TABLE DEPT_SAMPLE (FIELD1 XMLType);

INSERT INTO DEPT_SAMPLE VALUES
(XMLType('
<Department deptno="200">
  <DeptName>Purchasing</DeptName>
  <EmployeeList>
    <Ename>Scott</Ename>
  </EmployeeList>
</Department>'));



With only 1 employee, the EXTRACVALUE command works as expected.

SELECT extractValue(value(em), '/EmployeeList/Ename') AS ename
FROM DEPT_SAMPLE, table(XMLSequence(extract(FIELD1,'/Department/EmployeeList'))) em;

ENAME
---------------
Scott



Insert a second record. This record contains two Ename values under the EmployeeList.

INSERT INTO DEPT_SAMPLE VALUES (
XMLType('
<Department deptno="300">
  <DeptName>Sales</DeptName>
  <EmployeeList>
    <Ename>Robert</Ename>
    <Ename>Billy</Ename>
  </EmployeeList>
</Department>'));



Executing the same SQL Select as above will now produce an error.

SELECT extractValue(value(em), '/EmployeeList/Ename') AS ename
FROM DEPT_SAMPLE, table(XMLSequence(extract(FIELD1,'/Department/EmployeeList'))) em;

ORA-19025: EXTRACTVALUE returns value of only one node

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms