ORA-19025: EXTRACTVALUE returns value of only one node

I can successfully read from xml definition which has only one node. But when I am trying to read multiple node from xml definition I get the following error -

ORA-19025: EXTRACTVALUE returns value of only one node.

Below is my xml definition

<form xmlns:fr="http://orbeon.org/oxf/xml/form-runner" fr:data-format-version="4.0.0">
    <applicant>
        <userId>1234</userId>
        <userName>Dan Johnson</userName>
        <userEmail>[email protected]</userEmail>                                            
    </applicant>       
    <background>
        <additionalDuties>Nothing</additionalDuties>
           <grid-7>
            <position>Service desk analyst</position>
            <workplace>Chicago</workplace>
            <date>1234</date>
            <duties>NA</duties>
           </grid-7>
           <grid-7>
             <position>Service desk analyst 1</position>
             <workplace>New york</workplace>
             <date>123123</date>
             <duties>NA</duties>
           </grid-7>
    </background>
</form>

SQL query

SELECT extractvalue(xml, '//*/userId/text()') AS "E-number",
       extractvalue(xml, '//*/userName/text()') AS "Name",
       extractvalue(xml, '//*/userEmail/text()') AS "Email",
       extractvalue(xml, '//*/additionalDuties/text()') AS "Responsibilities",
       extractvalue(xml, '//*/position/text()') AS "Position",
       extractvalue(xml, '//*/workplace/text()') AS "Workplace",
       extractvalue(xml, '//*/date/text()') AS "Date",
       extractvalue(xml, '//*/duties/text()') AS "Duties"
  FROM form_data fd
 WHERE form = 'form-name'

Solution 1:

You can use XMLTABLE:

SELECT x.*
FROM   table_name t
       CROSS JOIN XMLTABLE(
         '/form/background/grid-7'
         PASSING XMLTYPE(xml)
         COLUMNS
           "E-number"         NUMBER        PATH './../../applicant/userId',     
           "Name"             VARCHAR2(150) PATH './../../applicant/userName',
           "Email"            VARCHAR2(150) PATH './../../applicant/userEmail',
           "Responsibilities" VARCHAR2(150) PATH './../additionalDuties',
           "Position"         VARCHAR2(150) PATH 'position',
           "Workplace"        VARCHAR2(150) PATH 'workplace',
           "Date"             NUMBER        PATH 'date',
           "Duties"           VARCHAR2(150) PATH 'duties' 
        ) x

Which, for the sample data:

CREATE TABLE table_name (xml) AS
SELECT TO_CLOB('<form xmlns:fr="http://orbeon.org/oxf/xml/form-runner" fr:data-format-version="4.0.0">
    <applicant>
        <userId>1234</userId>
        <userName>Dan Johnson</userName>
        <userEmail>[email protected]</userEmail>                                            
    </applicant>       
    <background>
        <additionalDuties>Nothing</additionalDuties>
        <grid-7>
            <position>Service desk analyst</position>
            <workplace>Chicago</workplace>
            <date>1234</date>
            <duties>NA</duties>
        </grid-7>
        <grid-7>
             <position>Service desk analyst 1</position>
             <workplace>New york</workplace>
             <date>123123</date>
             <duties>NA</duties>
        </grid-7>
    </background>
</form>') FROM DUAL

Outputs:

E-number	Name	Email	Responsibilities	Position	Workplace	Date	Duties
1234	Dan Johnson	[email protected]	Nothing	Service desk analyst	Chicago	1234	NA
1234	Dan Johnson	[email protected]	Nothing	Service desk analyst 1	New york	123123	NA

db<>fiddle here

Solution 2:

You can use XMLTABLE which is recommended by Oracle rather than deprecated function EXTRACTVALUE such as

WITH form_data( xml ) AS
(
 SELECT XMLType(
          REPLACE(
                  '<form xmlns:fr="http://orbeon.org/oxf/xml/form-runner" fr:data-format-version="4.0.0">
                         the rest of your XML is embedded here
                   </form>','grid-','grid')
          )
   FROM dual
)
SELECT "E-number", "Name", "Email", "Responsibilities", 
       "Position", "Workplace","Date","Duties"
  FROM form_data,
  XMLTABLE('/form/applicant'
      PASSING xml
      COLUMNS 
             "E-number" VARCHAR2(150) PATH 'userId',
             "Name"     VARCHAR2(150) PATH 'userName',
             "Email"    VARCHAR2(150) PATH 'userEmail'
         ),
  XMLTABLE('/form/background/grid7'
      PASSING xml
      COLUMNS          
             "Responsibilities" VARCHAR2(150) PATH 'duties',
             "Position"         VARCHAR2(150) PATH 'position',
             "Workplace"        VARCHAR2(150) PATH 'workplace',
             "Date"             VARCHAR2(150) PATH 'date',
             "Duties"           VARCHAR2(150) PATH 'duties' 
         )   

Demo