Avatar of j2911
j2911
 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.
Oracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.