extracting xmltype in pl/sql

Question

I'm new in pl/sql and to start working with xmltype i followed this example on https://docs.oracle.com/cd/B10501_01/appdev.920/a96616/arxml24.htm

It looks like this

CREATE TABLE Xml_tab ( xmlval xmltype);

INSERT INTO Xml_tab VALUES (
xmltype('<?xml version="1.0"?>
           <EMP>
              <EMPNO>221</EMPNO>
              <ENAME>John</ENAME>
           </EMP>'));

INSERT INTO Xml_tab VALUES (
xmltype('<?xml version="1.0"?>
           <PO>
              <PONO>331</PONO>
              <PONAME>PO_1</PONAME>
           </PO>'));

-- now extract the numerical values for the employee numbers

SELECT e.xmlval.extract('//EMPNO/text()').getNumVal() as empno
    FROM Xml_tab 
    WHERE e.xmlval.existsnode('/EMP/EMPNO')  = 1;

When I'm able to create table and insert rows into it. But when i try to run last procedure i get ORA-22950: cannot ORDER objects without MAP or ORDER method

Why is it happening? Thank you in advance.

Answer 1

I don't know what exactly you were trying to achieve, but this is how EXTRACT and EXISTSNODE function could be used to retrieve numerical values for the employee numbers - EMPNO

SELECT extract(xmlval,'/EMP/EMPNO/text()').getNumberVal() as empno
    FROM Xml_tab WHERE
    existsnode(xmlval,'/EMP/EMPNO')  = 1 ;

Demo