1 Reply Latest reply on Dec 30, 2018 10:06 AM by cormaco

    ORA-19025: EXTRACTVALUE returns value of only one node

    950599

      I know this is a well knowN error, but i need some help with my xml query.

       

      My Xml table;

      CREATE TABLE xml_tab (   id        NUMBER,   xml_data  XMLTYPE );

      INSERT INTO xml_tab VALUES (1,XMLType(bfilename('XMLDIR', 'jobs.xml'), nls_charset_id('AL32UTF8')));

       

      My jobs.xml file

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

      <Jobsdata>

          <jobList>

              <jobData>

                  <job>

                      <name>JOBNAME10</name>

                      <schedule>

                          <frequency>IMMEDIATE</frequency>

                      </schedule>

                      <targetType>host</targetType>

                      <targets>

                          <name>node100.localdomain</name>

                          <type>host</type>

                      </targets>

                      <targets>

                          <name>node200.localdomain</name>

                          <type>host</type>

                      </targets>

                  </job>

              </jobData>

              <jobData>

                  <job>

                      <name>JOBNAME20</name>

                      <schedule>

                          <frequency>REPEAT_BY_DAYS</frequency>

                      </schedule>

                      <status>ACTIVE</status>

                      <targetType>host</targetType>

                      <targets>

                          <name>node300.localdomain</name>

                          <type>host</type>

                      </targets>

                  </job>

              </jobData>

              <jobData>

                  <job>

                      <name>JOBNAME300</name>

                      <schedule>

                          <days>3</days>

                          <days>5</days>

                          <frequency>WEEKLY</frequency>

                      </schedule>

                      <targets>

                          <name>node400.localdomain</name>

                          <type>host</type>

                      </targets>

                  </job>

              </jobData>

          </jobList>

      </Jobsdata>

       

       

      If i want to select the jobname and the targets, it works fine::

       

      SELECT jb.jobname, tg.targetname
      FROM  xml_tab x,      
      XMLTABLE('/Jobsdata/jobList/jobData/job'        
      PASSING x.xml_data        
      COLUMNS          
         jobname      PATH 'name'   ,

        targets      XMLType PATH 'targets') jb,        
      XMLTABLE('/targets' passing  jb.targets  
      columns     targetname path 'name') tg;

       

      But now i also want to select the "days" value and then i receive an error.

       

      SELECT jb.jobname, tg.targetname,dy.days FROM  xml_tab x,      
      XMLTABLE('/Jobsdata/jobList/jobData/job'        
      PASSING x.xml_data        
      COLUMNS jobname      PATH 'name' ,
      schedule      XMLType PATH 'schedule' ,
      targets      XMLType PATH 'targets') jb,       

      XMLTABLE('/targets' passing  jb.targets        

      COLUMNS     targetname path 'name') tg,

      XMLTABLE('/schedule' passing  jb.schedule        

      COLUMNS                 days path 'days') dy;

       

      *ERROR at line 12:  ORA-19025: EXTRACTVALUE returns value of only one node

       

      I think understand why this is going wrong, but i struggle for days now how to solve this.

       

      Any help is appreciated Regards,

       

      Harry

        • 1. Re: ORA-19025: EXTRACTVALUE returns value of only one node
          cormaco

          Hi Harry,

           

          here is the corrected version of your second query, I also added datatype for the columns where left them out:

          SELECT 
              jb.jobname, tg.targetname,dy.days 
          FROM xml_tab x,      
          XMLTABLE(
              '/Jobsdata/jobList/jobData/job'        
              PASSING x.xml_data        
              COLUMNS 
                  jobname     varchar2(10) PATH 'name' ,
                  schedule    XMLType      PATH 'schedule' ,
                  targets     XMLType      PATH 'targets') jb,       
          XMLTABLE('/targets' 
              passing  jb.targets        
              COLUMNS     
                  targetname varchar2(20) path 'name') tg,
          XMLTABLE('/schedule/days' 
              passing  jb.schedule        
              COLUMNS
                  days number(3) path '.') dy;
          
          
          JOBNAME    TARGETNAME                 DAYS
          ---------- -------------------- ----------
          JOBNAME300 node400.localdomain           3
          JOBNAME300 node400.localdomain           5
          
          
          
          

          This query returns only values from nodes with days, if you want the other nodes as well, use an outer join on the last xmltable:

          XMLTABLE('/schedule/days' 
              passing  jb.schedule        
              COLUMNS
                  days number(3) path '.') (+) dy
          
          
          JOBNAME    TARGETNAME                 DAYS
          ---------- -------------------- ----------
          JOBNAME10  node100.localdomain            
          JOBNAME10  node200.localdomain            
          JOBNAME20  node300.localdomain            
          JOBNAME300 node400.localdomain           3
          JOBNAME300 node400.localdomain           5