Database Software

Hi,

I am dumping some xml data coming from a webservice into our database, inside an xmltype column. Next thing we need to do is to extract the values from the xml structure, which consists of Tables and the corresponding data for each Table (CODIGO_BUQUE and NOMBRE_BUQUE).

Everything works fine when we are dealing with moderate amounts of data. The webservice is parametrizable by date, and the lower the date, the more data it will provide. If I ask for the data from three months ago, I will get 800 Tables and I will manage to dump the data into a database table within seconds.

My problem is when I ask the data from 20 years ago in order to make an initial load of data. The xml gets populated with the data coming from the webservice, but the procedure that extracts the data from the xml structure and dumps it into the database and that previously took just a few seconds, runs endlessly now.

I am quite sure that the reason is the giant size of the xmltype variable that has been built from the webservice, but I am clueless on how to resolve the problem.

Do you have any idea? Here is the query where the procedure get stuck:


select extract(value(d), '//Table/CODIGO_BUQUE/text()', 'xmlns="Inspeccion"').getStringVal() as CODIGO_BUQUE,
extractvalue(value(d), '//Table/NOMBRE_BUQUE/text()', 'xmlns="Inspeccion"') as NOMBRE_BUQUE
from csp98bd.xml_data_tab t,
table(xmlsequence(extract(extract(t.xml_data, '/soap:Envelope/soap:Body/getCensosFlotaResponse/getCensosFlotaResult/diffgr:diffgram/*',--getCensosFlotaResponse/getCensosFlotaResult/diffgr:diffgram/*',--/diffgr:diffgram/CENSO/Table/CODIGO_BUQUE',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns="http://tempuri.org/"
xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"
')
,'/CENSO/*', 'xmlns="Inspeccion"'))) d;


I wish everything was clear enough. If not, please, tell me and I will try to make it clearer.

Thanks,
Fernando.