asked on

Stored Procedure help with XML Clob.

Can someone please help me create an Oracle Stored Procedure for a Clob that contains XML?

create global temporary table SHIP_INFO_TEMP
on commit preserve rows as
with myXML as (
select test_id||'-'||ship_seq_nbr as scnro_id, TRKNG_NBR,
xmltype(BASELINE)
xmlcol from IAT.ship_info
where isvalid(baseline) = 1)
select scnro_id, TRKNG_NBR, TRKNG_NBR,
extractvalue(xmlcol,'//MNBR') "MN",
extractvalue(xmlcol,'//P1') "P1",
extractvalue(xmlcol,'//RECPCNTRYCD') "Dest",
extractvalue(xmlcol,'//RECPNM') "Recipient_Name",
extractvalue(xmlcol,'//SNDRCNTRYCD') "Origin",
extractvalue(xmlcol,'//SHIPPING_DEVICE') "Software",
extractvalue(xmlcol,'//SVCTYPCD') "Service"
from myXML;

I need it to parse this XML faster, so I can use it in a query. It is taking me 30 minutes to run a query when it has to use this parsed data. I'm not sure what is the best aproach to handle this. I would be open to suggestions, if I am barking up the wrong tree. However it was suggested to me the Stored procedures would work better and faster.

What is needed is I need to be able to lookup the data that is stored in an XML in this clob.

If a stored procedure would work --- I need to know how to do that.

Thank you.

j2911

ASKER

Or maybe I should have asked this question: "how to use the xmltype and call an Oracle stored procedure?"

j2911

ASKER

Is so how would I do that? I'm searching for examples and cannot find any.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

j2911

ASKER

I have xml that is stored in a clob and it contains lots of information. So what I did was create view with the above original statement that was only pulling what I needed to pull out of the entire xml.

Now that I have that view, I have a query that pulls from this data from the view. It only take a couple of minutes to create the view but when I try to pull that data that I need into my other query it takes nearly 30 minutes or more for it to run. In my query, I am only pulling about 4000 rows of data. Using the same query and not pulling that data from the clob, it would only take me around 4-5 minutes.

So it slows down tremedously when I use the data that I retrieved from the xml in the clob. I am trying to figure the best way to use that data that is stored in xml in a clob. I need to use it all of the time. I wanted it to be stored in a table but every day it must be updated with new data. It was suggested to me that I should use stored procedures but I am not sure how that would work in this situation.

j2911

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for j2911's comment #a38016669

for the following reason:

thanks

j2911

ASKER

sorry but much has happened since I posted this question. Please close this.

Sean Stuber

rather than accepting your own post, please accept an answer (or answers) that helped you.