Skip to Main Content
  • Questions
  • Extract xml node value with existnode filter condition

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Asit.

Asked: July 29, 2020 - 11:15 am UTC

Answered by: Chris Saxon - Last updated: July 31, 2020 - 9:58 am UTC

Category: SQL - Version: 12c

Viewed 100+ times

You Asked

Hi Chris,
Appreciate for your support. I have a requirement where want to return specific node values if a particular node exist. Example - for the below XML, my output will be 2 records because (Jobsdata/jobList/jobData/job/document) node exist 2 times and returning nodes are (job/document/fileID and job/directoryid ). O/p --> 100,D100
200,D200

Please find the XML table in the Live SQL link .
My O/P should be as given below-

id   fileID   directoryid
1    100      D100
1    200      D200


Filter Criteria - I don't want to display the directoryid : D300 because there is no <document> tag. Hence, file ID and directoryid should be displayed as <document> tag available for those.

Thanks,Asit

with LiveSQL Test Case:

and we said...

You can use XMLTable to convert the document to relational rows-and-columns:

CREATE TABLE xml_tab (   id        NUMBER,   xml_data  XMLTYPE );

INSERT INTO xml_tab VALUES(1,xmltype('<?xml version="1.0" encoding="UTF-8" standalone="yes"?> 
<Jobsdata> 
    <jobList> 
        <jobData> 
            <job> 
                <name>JOBNAME10</name> 
                <document> 
                 <fileID>100</fileID> 
                </document> 
                <directoryid>D100</directoryid> 
                <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> 
                <document> 
                 <fileID>200</fileID> 
                </document> 
                <directoryid>D200</directoryid> 
                <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> 
                <directoryid>D300</directoryid> 
                <schedule> 
                    <days>3</days> 
                    <days>5</days> 
                    <frequency>WEEKLY</frequency> 
                </schedule> 
                <targets> 
                    <name>node400.localdomain</name> 
                    <type>host</type> 
                </targets> 
            </job> 
        </jobData> 
    </jobList> 
</Jobsdata>'));

select id, x.*
from   xml_tab, xmltable (
  'Jobsdata/jobList/jobData/job' passing xml_data 
  columns 
    fileID path 'document/fileID',
    directoryid path 'directoryid'
) x;

ID    FILEID    DIRECTORYID   
    1 100       D100           
    1 200       D200           
    1 <null>    D300 


From there it's just a matter of filtering out the rows where fileid is null!

and you rated our response

  (3 ratings)

Reviews

July 29, 2020 - 8:05 pm UTC

Reviewer: A reader from London

Thanks a lot Chris.
Appreciate for the quick response & easy solution !


July 30, 2020 - 11:39 am UTC

Reviewer: Asit Sahu from London

Hi Chris,
The query is not working for multiple nodes. I have multiple <Document><FileID></FileID></Document>. How to retrieve all values ?

Thanks,
Asit

INSERT INTO xml_tab VALUES(4,xmltype('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<Jobsdata>

    <jobList>

        <jobData>

            <job>

                <name>JOBNAME10</name>
                <document>
                 <fileID>100</fileID>
                </document>
                <document>
                 <fileID>1000</fileID>
                </document>
                <directoryid>D100</directoryid>

                <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>
                <document>
                 <fileID>200</fileID>
                </document>
                <directoryid>D200</directoryid>

                <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>
                <directoryid>D300</directoryid>

                <schedule>

                    <days>3</days>

                    <days>5</days>

                    <frequency>WEEKLY</frequency>

                </schedule>

                <targets>

                    <name>node400.localdomain</name>

                    <type>host</type>

                </targets>

            </job>

        </jobData>

    </jobList>

</Jobsdata>'));

select id, x.*
from   xml_tab, xmltable (
  'Jobsdata/jobList/jobData/job' passing xml_data 
  columns 
    fileID path 'document/fileID',
    directoryid path 'directoryid'
) x
where id<>4;

Chris Saxon

Followup  

July 30, 2020 - 1:14 pm UTC

What does "not working" mean and what exactly is it you want to happen here?

July 30, 2020 - 1:48 pm UTC

Reviewer: Asit from London

Please refer my XML table here -
https://livesql.oracle.com/apex/livesql/s/kf5cb2omf2m84b8zsbv8y9q9j

Getting Error for below query.(ora-19025 extractvalue returns value).
select id, x.*
from xml_tab, xmltable (
'Jobsdata/jobList/jobData/job' passing xml_data
columns
fileID path 'document/fileID',
directoryid path 'directoryid'
) x
;

Expected Output-
ID FILEID DIRECTORYID
4 100 D100
4 200 D200
4 201 D200
4 202 D200
4 203 D200
4 <null> D300

Chris Saxon

Followup  

July 31, 2020 - 9:58 am UTC

The requested output contains values not in the XML (201, 202, 203)! So I'm unclear exactly what you want.

I'm sure you can get there by using some combination of array notation for document:

- document[1] => get the first document element under job
- document[*] => get all the document elements under job

e.g.:

select id, x.*
from xml_tab, xmltable (
  'Jobsdata/jobList/jobData/job' passing xml_data
  columns
    fileID path 'document[1]/fileID',
    directoryid path 'directoryid'
) x;

        ID FILEID                         DIRECTORYID         
---------- ------------------------------ --------------------
         4 100                            D100                
         4 200                            D200                
         4 <null>                         D300   

select id, x.*
from xml_tab, xmltable (
  'Jobsdata/jobList/jobData/job' passing xml_data
  columns
    fileID path 'document[*]/fileID',
    directoryid path 'directoryid'
) x;

        ID FILEID                         DIRECTORYID         
---------- ------------------------------ --------------------
         4 1001000                        D100                
         4 200                            D200                
         4 <null>                         D300 

select id, x.*
from xml_tab, xmltable (
  'Jobsdata/jobList/jobData/job' passing xml_data
  columns
    fileID xmltype path 'document[*]/fileID',
    directoryid path 'directoryid'
) x;

        ID FILEID                         DIRECTORYID         
---------- ------------------------------ --------------------
         4 <fileID>100</fileID>           D100                
           <fileID>1000</fileID>                              

         4 <fileID>200</fileID>           D200                

         4 <null>                         D300 

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.