ORA-19025: EXTRACTVALUE returns value of only one node
(Doc ID 1436776.1)
Last updated on JANUARY 07, 2020
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A 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 nodeCause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |