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
729 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 74

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 77

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 74

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 74

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo 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…

710 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