troubleshooting Question

Stored Procedure help with XML Clob.

Avatar of j2911
j2911 asked on
Oracle Database
7 Comments1 Solution625 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros