Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
788 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 200 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 1800 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

718 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