Skip to Main Content
  • Questions

  • Extract xml node value with existnode filter condition

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Asit.

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

Last updated: July 31, 2020 - 9:58 am UTC

Version: 12c

Viewed 1000+ 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:
https://livesql.oracle.com/apex/livesql/s/kfy9epa7pakef21h7wxugpfwu

and Chris 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!

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 29, 2020 - 8:05 pm UTC

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


Asit Sahu, July 30, 2020 - 11:39 am UTC

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
July 30, 2020 - 1:14 pm UTC

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

Asit, July 30, 2020 - 1:48 pm UTC

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
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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.