Hi,
We have a couple of routines still using the deprecated EXTRACTVALUE function so we're looking at replacing them with the suggested XMLTABLE method.
My DB version is 19.9 EE.
The thing we've noticed is that the XMLTABLE version seems to be less performant than the EXTRACTVALUE version. I'm not particularly skilled at XQuery and XML in general, so maybe there's some explanation or better way to do.
Here's the test case:
create or replace package test_pkg as
function ExtractV_OldMethod(i_xml in xmltype, i_tag_name in varchar2)
return varchar2;
function ExtractV_NewMethod(i_xml in xmltype, i_tag_name in varchar2)
return varchar2;
end test_pkg;
/
create or replace package body test_pkg as
function ExtractV_OldMethod(i_xml in xmltype, i_tag_name in varchar2)
return varchar2 is
v_res varchar2(4000);
begin
select extractvalue(i_xml,'/*/'||i_tag_name) into v_res from dual;
return v_res;
end ExtractV_OldMethod;
function ExtractV_NewMethod(i_xml in xmltype, i_tag_name in varchar2)
return varchar2 is
v_path varchar2(4000) := '/*/'||i_tag_name;
v_res varchar2(4000);
begin
select i_tag_name into v_res
from xmltable(v_path passing i_xml
columns i_tag_name varchar2(4000) path '.'
);
return v_res;
end ExtractV_NewMethod;
end test_pkg;
/
And the test:
Using old method
set timing on
set serveroutput on
declare
v_xml xmltype := xmltype('<root>
<tag1>1</tag1>
<tag2>2</tag2>
<tag3>3</tag3>
</root>');
v_res varchar2(4000);
begin
for i in 1..1000 loop
v_res := test_pkg.extractV_OldMethod(v_xml,'tag2');
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.38
Using new method
set timing on
set serveroutput on
declare
v_xml xmltype := xmltype('<root>
<tag1>1</tag1>
<tag2>2</tag2>
<tag3>3</tag3>
</root>');
v_res varchar2(4000);
begin
for i in 1..1000 loop
v_res := test_pkg.extractV_NewMethod(v_xml,'tag2');
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.03
As you can see the difference is quite significant. Am I doing it wrong?