Solved

how to generate tabular data with xml table join with other row data table using Oracle 11g R2 PLSQL Stored Procedure?

Posted on 2012-04-08
6
674 Views
Last Modified: 2012-06-27
hi,

i have this xml data stored as clob in xmltable call COMPTABLE:-

<?xml version="1.0" encoding="UTF-8"?>
<company>
    <emp id="001">
        <emp id="002">
            <emp id="004"/>
            <emp id="005"/>
        </emp>
        <emp id="003">
            <emp id="006"/>
            <emp id="007"/>
        </emp>
    </emp>
</company>

Open in new window


and i have related table call COMPDETAIL which stored those emp id and other details in relational format,
Data with column name as follow:-

empid,empname,empage
001,KEN,20
002,RYU,21
003,BEN,22
004,IVY,23
005,JAK,24
006,MATT,25
007,STEVE,26

I want to query the data by reading the xml's criteria where example:-
get the data for empid = 003 and its descendant in tabular format:-

the result will return me
003,BEN,22
006,MATT,25
007,STEVE,26

my environment is
Oracle 11gR2, 32 bit
Using Stored Procedure
Windows server 2003

please note that this query will be written entirely in stored procedure PL/SQL.

please advice
0
Comment
Question by:Ronjer
  • 3
  • 2
6 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 37824655
S ELECT   *
    FROM compdetail
   WHERE empid IN (SELECT EXTRACTVALUE(x.COLUMN_VALUE, '/emp/@id') empid
                     FROM comptable c,
                          XMLTABLE('//emp[@id="003"]'
                                   PASSING xmltype(xml)
                                  ) x
                   UNION ALL
                   SELECT EXTRACTVALUE(x.COLUMN_VALUE, '/emp/@id') empid
                     FROM comptable c,
                          XMLTABLE('//emp[@id="003"]/emp'
                                   PASSING xmltype(xml)
                                  ) x)
ORDER BY empname
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 50 total points
ID: 37824721
I was thinking the same thing but sdstuber beat me to it.

The main different is you can eliminate the first extract since 003 is a 'known'.

Tweaking his code above:

SELECT   *
    FROM compdetail
   WHERE empid IN (SELECT '003' from dual
                   UNION ALL
                   SELECT EXTRACTVALUE(x.COLUMN_VALUE, '/emp/@id') empid
                     FROM comptable c,
                          XMLTABLE('//emp[@id="003"]/emp'
                                   PASSING xmltype(xml)
                                  ) x)
ORDER BY empname
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 450 total points
ID: 37824760
you can make it even simpler by using descendant-or-self  to pick up 003 and it's children in one step

SELECT   *
    FROM compdetail
   WHERE empid IN (SELECT EXTRACTVALUE(x.COLUMN_VALUE, '/emp/@id') empid
                     FROM comptable c,
                          XMLTABLE('//emp[@id="003"]/descendant-or-self::node()'
                                   PASSING xmltype(xml)
                                  ) x
                   )
ORDER BY empname;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Ronjer
ID: 37826701
hi,

i tried to run the query given:-

SELECT   *
    FROM compdetail
   WHERE empid IN (SELECT EXTRACTVALUE(x.COLUMN_VALUE, '/emp/@id') empid
                     FROM comptable c,
                          XMLTABLE('//emp[@id="003"]/descendant-or-self::node()'
                                   PASSING xmltype(xml)
                                  ) x
                   )
ORDER BY empname;

Open in new window


, but it throws error invalid identifier, on that particular line "PASSING xmltype(xml)",
doesn't xmltable query pass content? what is that xml argument mean inside the xmltype()?

please advice
0
 

Author Comment

by:Ronjer
ID: 37826854
hi fellow experts,

i tweak the query a bit and now able to select the results i wanted by:-
SELECT   *
    FROM compdetail
   WHERE empid IN (SELECT EXTRACTVALUE(x.COLUMN_VALUE, '/emp/@id') empid
                     FROM comptable c,
                          XMLTABLE('//emp[@id="003"]/descendant-or-self::node()'
                                   PASSING OBJECT_VALUE
                                  ) x
                   )
ORDER BY empname;

Open in new window


it works, thanks for contribution
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37827277
>>>>  PASSING OBJECT_VALUE


this is because your don't have an table with xml in a clob column,  you have a table of xml type.
I guess slightwv and I must have both misunderstood your question.  Glad you got it worked out though.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle - How to analyze data using DATE COLUMN? 7 74
sql query 5 69
Need a replacement data type in Oracle 6 65
Oracle and DateTime math 6 26
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now