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