Oracle select with function call

Hi experts, i have this table called employee with 4 columns.name varchar, role varchar, salary varchar and details clob (xml).

Eg of clob xml : <employee><detail><add1>abc</add1>
<add2>abc</add2><add3>abc</add3> <employee><detail>

Until now i was able to select name, role, salary and xml node add1 using a select query with xmltable function.Now as per new requirement, i need to extract add2 and add3 aswel.What i need is generic function which  i can use in my select query that takes in the details clob column and internally would call xmltable function and return all three nodes add1,add2,add3.using this function in my slect i want to return add1,add2,add3 along with name , role and salary.Pls help.
DevildibAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DevildibAuthor Commented:
Updated : Eg of clob xml : <employee><detail><add1>abc</add1>
<add2>abc</add2><add3>abc</add3> <employee><detail></employee>
0
slightwv (䄆 Netminder) Commented:
I'm sure I'm missing the actual requirement but to extract additional values from the XML, just add the nodes to the XMLTABLE list of columns.

Just like your last question:
Sample data and expected results help a lot.
0
DevildibAuthor Commented:
How do we return all three nodes along with other columns using select statement? I have given you the data.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
You already have an XMLTABLE call that returns add1.

Returning add2 and add3 is just adding two more columns clauses in the XMLTABLE call.
0
DevildibAuthor Commented:
Select name, role, salary , oldfunction(clob data) from eployee


Select name, role, salary, newfunction(clobdata) from employee

Will the second select give me what i want? How is newfunction going to return add1,add2, add3??dunction normally returns single value right?
0
slightwv (䄆 Netminder) Commented:
I don't know what oldfunction and newfunction are.

You referenced XMLTABLE.  That isn't used inline as a column function.

There is no need for a 'custom' function.

In the select you have in the XMLTABLE piece you should have add1 under the columns section.  Just copy and paste that line two more times and change the two new lines to add2 and add3.

Post the query you have and I can add the two new columns.
0
DevildibAuthor Commented:
I am trying to help you in making the data easy and simple.anyways oldfunction is what i already have.withing oldfunction the xmltable logic is written to extract add1.and i am using this custom function in order to handle some exceptions.in newfunction i can build on by adding add2 and add3 in the xmltable select.But finally i would require this function return all three add1,add2and add3 values.hope you are getting me now.in nutshell, how do i return multiple values out of a function that is used in a select query?
0
slightwv (䄆 Netminder) Commented:
>>I am trying to help you in making the data easy and simple

But that is actually confusing me...  It masks some of the actual requirements.

>>how do i return multiple values out of a function that is used in a select query?

You don't.

You can return XML from the function and use XMLTABLE in the outer function to parse the nodes into column values.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DevildibAuthor Commented:
Thanks.but thats not the case.i have given you all whats needed for this question.
0
DevildibAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for flyhighrohit12's comment #a41216174

for the following reason:

All ok
0
DevildibAuthor Commented:
All ok
0
slightwv (䄆 Netminder) Commented:
I realize you understand the problem you want solved.  What you have provide might not be all that clear to the people reading it on the other end.

If you have written a custom function that does some 'magic' to return one value and not you need to return three:
As I mentioned above:
1:  Have the function return the three values as XML and use XMLTABLE in the calling query.
or
2:  Change the function to allow you to pass in the node value you want, then return the single value.  The problem here is you would need to call the function three times:
Select name, role, salary, newfunction(clobdata,'add1'),newfunction(clobdata,'add2'),newfunction(clobdata,'add3') from employee
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.