Improve performance of XML parsing, please help

Hi gurus,

I'm having big trouble trying to improve performance of XML parsing. With an XML of size 30k it takes around ONE SECOND.

I'm using extractvalue() to extract values. Here is the code which inserts 3 rows in AMD_ADDRESS_NAME table :

declare
  myClobWithXML CLOB;
begin
  select CLOB_VAL into myClobWithXML from ADDRESS_TABLE where ID=1;

for c2rec in (SELECT
extractvalue(x,xpathPrefix || 'nameInfo['||nr||']/nameType/text()') as nameType,
extractvalue(x,xpathPrefix || 'nameInfo['||nr||']/nameLine1/text()') as nameLine1,
extractvalue(x,xpathPrefix || 'nameInfo['||nr||']/nameLine2/text()') as nameLine2,
extractvalue(x,xpathPrefix ||
'nameInfo['||nr||']/effectiveDate/text()') as effectiveDate,
extractvalue(x,xpathPrefix ||
'nameInfo['||nr||']/expirationDate/text()') as expirationDate
FROM (SELECT xmltype(QUEUE_INTERFACE.XML_GLOBAL_VAR) x FROM dual) ---FROM (SELECT xmltype( myClobWithXML ) x FROM dual) , qt_dummy_numbers
where extractvalue(x,xpathPrefix ||
'nameInfo['||nr||']/nameType/text()') IS NOT NULL
and extractvalue(x,xpathPrefix ||
'addressInfo['||nr||']/addressType/text()') IS NOT NULL)
--would be: table(XMLSequence())
 loop

  INSERT INTO AMD_ADDRESS_NAME (ID, NAME_TYPE, NAME_LINE1, NAME_LINE2, EFFECTIVE_DATE, EXPIRATION_DATE)
  VALUES (SEQ_ADDRESS_NAME.NEXTVAL, c2rec.nameType, c2rec.nameLine1, c2rec.nameLine2, to_date(c2rec.effectiveDate,'mm/dd/yyyy hh24:mi:ss'),to_date(c2rec.expirationDate,'mm/dd/yyyy hh24:mi:ss'));

 end loop;

 commit;
end;

In this example I select only a few values ( nameType, nameLine1, nameLine2, effectiveDate, expirationDate ). For the real case I select around 30 values.

Problems:
1)Is there other way I could parse the XML to be more efficient as time?
2)I saw that increasing the number of values obtained with extractValue the performance of parsing decreases. Could be that for each value obtained with extractValue() it parses the XML?

Many Thanks,
Beri
PL/SQL Developer Received on Fri Apr 29 2005 - 06:17:48 CDT