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
717 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

680 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