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
660 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>>>  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

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.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

771 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

13 Experts available now in Live!

Get 1:1 Help Now