Hi,
I'm trying to improve the performance of a piece of code where extracting data
from an XML document is involved.
Here is a simplified code with the XML document defined in it.
I've already achieved about 30% performance boost by rewriting SELECTs using EXTRACTVALUE
into a form using the XMLTYPE member functions EXTRACT and GETSTRINGVAL.
(see the commented lines).
declare
ivkX XMLType;
nperiodsX XMLType;
suminsX NUMBER(10);
nperiods_tab nperiods_T;
begin
ivkX:=xmltype(
'<ivk>
<insurance>
<sumins>20596</sumins>
<yearsins>56</yearsins>
<daysins>156</daysins>
</insurance>
<nperiods>
<nperiod datefrom="1965-10-03" dateto="1965-10-03" days="1" />
<nperiod datefrom="2005-09-01" dateto="2009-12-31" days="1583" />
<nperiod datefrom="2013-01-01" dateto="2013-12-31" days="8" />
</nperiods>
</ivk>');
-- usual
SELECT extractvalue(ivkX, '//ivk/insurance/sumins') INTO suminsX FROM DUAL;
DBMS_OUTPUT.PUT_LINE(suminsX);
-- preferred, faster, avoids context switch PLSQL <--> SQL
suminsX:=ivkX.EXTRACT('//ivk/insurance/sumins/text()').GETSTRINGVAL();
DBMS_OUTPUT.PUT_LINE(suminsX);
nperiodsX:=ivkX.EXTRACT('//ivk/nperiods');
nperiodsX.ToObject(nperiods_tab);
end;
/
ERROR at line 1:
ORA-19031: XML element or attribute nperiod does not match any in type IDB_MIG.NPERIODS_T
ORA-06512: at "SYS.XMLTYPE", line 196
ORA-06512: at line 33
I'd like to do the same or similar with attributes of the repeating elements, like the nperiod elements here.
But I got stuck with the definition of an appropriate object type.
Here is one of my attempts of defining the object types to extract the values of the repeating
attributes datefrom, dateto and days in the <nperiods> element into an object table.
CREATE OR REPLACE TYPE nperiod_T AS OBJECT("@datefrom" varchar2(10),"@dateto" varchar2(10),"@days" varchar2(10));
/
CREATE OR REPLACE TYPE nperiods_T AS OBJECT("nperiod" nperiod_T);
/
I've tried various other definitions of the nperiods_T object type, but I keep getting
either the ORA-19031 error or type inconsistency.
Could you please tell me how I should construct the object type to be able to do that?
The database I use is Oracle 12.1.0.2.
Thank you,
Pavel
I don't know how to do exactly what you're looking for; I've tried various things with no luck.
That said, some pointers which may help:
The object will have an array of nperiod items, so you'll need to declare a nested table type. The current objects only allow one nperiod element under nperiods.
You can loop through the nperiod elements, assigning each to an object as you go:
create or replace type "nperiod" force as object(
"@datefrom" varchar2(10),"@dateto" varchar2(10),"@days" varchar2(10)
);
/
declare
ivkX XMLType;
nperiodsX XMLType;
x xmltype;
n number;
p "nperiod";
begin
ivkX:=xmltype(
'<ivk>
<insurance>
<sumins>20596</sumins>
<yearsins>56</yearsins>
<daysins>156</daysins>
</insurance>
<nperiods>
<nperiod datefrom="1965-10-03" dateto="1965-10-03" days="1" />
<nperiod datefrom="2005-09-01" dateto="2009-12-31" days="1583" />
<nperiod datefrom="2013-01-01" dateto="2013-12-31" days="8" />
</nperiods>
</ivk>');
nperiodsX:=ivkX.EXTRACT('//ivk/nperiods');
select xmlcast (
xmlquery (
'count(//nperiods/*)' passing nperiodsX returning content
) as number
)
into n
from dual;
for i in 1 .. n loop
x := nperiodsX.EXTRACT('//nperiod['||i||']');
x.toObject ( p );
dbms_output.put_line ( p."@datefrom" );
end loop;
end;
/
1965-10-03
2005-09-01
2013-01-01The XMLCast ... XMLQuery select is to get the number of elements under nperiods; there may be a way to do this in PL/SQL, but I can't find it right now!
It may also be worth looking into XMLTable. This converts XML to relational rows-and-columns using SQL. This will have a context switch, but it may enable you to reduce the number of extract calls your making, giving a good win:
select * from xmltable (
'ivk/nperiods/nperiod'
passing xmltype ( '<ivk>
<insurance>
<sumins>20596</sumins>
<yearsins>56</yearsins>
<daysins>156</daysins>
</insurance>
<nperiods>
<nperiod datefrom="1965-10-03" dateto="1965-10-03" days="1" />
<nperiod datefrom="2005-09-01" dateto="2009-12-31" days="1583" />
<nperiod datefrom="2013-01-01" dateto="2013-12-31" days="8" />
</nperiods>
</ivk>' )
columns
datefrom varchar2(10) path '@datefrom',
dateto varchar2(10) path '@dateto',
days integer path '@days'
);
DATEFROM DATETO DAYS
1965-10-03 1965-10-03 1
2005-09-01 2009-12-31 1583
2013-01-01 2013-12-31 8