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,'//MNB R') "MN",
extractvalue(xmlcol,'//P1' ) "P1",
extractvalue(xmlcol,'//REC PCNTRYCD') "Dest",
extractvalue(xmlcol,'//REC PNM') "Recipient_Name",
extractvalue(xmlcol,'//SND RCNTRYCD') "Origin",
extractvalue(xmlcol,'//SHI PPING_DEVI CE') "Software",
extractvalue(xmlcol,'//SVC TYPCD') "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.
create global temporary table SHIP_INFO_TEMP
on commit preserve rows as
with myXML as (
select test_id||'-'||ship_seq_nbr
xmltype(BASELINE)
xmlcol from IAT.ship_info
where isvalid(baseline) = 1)
select scnro_id, TRKNG_NBR, TRKNG_NBR,
extractvalue(xmlcol,'//MNB
extractvalue(xmlcol,'//P1'
extractvalue(xmlcol,'//REC
extractvalue(xmlcol,'//REC
extractvalue(xmlcol,'//SND
extractvalue(xmlcol,'//SHI
extractvalue(xmlcol,'//SVC
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
Is so how would I do that? I'm searching for examples and cannot find any.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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
Accepted answer: 0 points for j2911's comment #a38016669
for the following reason:
thanks
ASKER
sorry but much has happened since I posted this question. Please close this.
rather than accepting your own post, please accept an answer (or answers) that helped you.
ASKER