Update Oracle GDB docs: EXTRACTVALUE is deprecated, use XMLTABLE instead (faster)

1068
0
06-28-2022 08:18 AM
Status: Open
Labels (1)
Bud
by
Notable Contributor

Oracle enterprise GDBs:


The enterprise GDB system table docs tell us to use the Oracle EXTRACTVALUE function when extracting GDB system table data via SQL.

But it looks like EXTRACTVALUE has been deprecated:

Oracle 11g R2 docs: 

The EXTRACTVALUE function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLTABLE function, or the XMLCAST and XMLQUERY functions instead. 

11g R2 release date: 2009


EXTRACTVALUE vs XMLTABLE

Speed comparison: (Oracle 18c)


Select domain codes/descriptions using EXTRACTVALUE (the old way):

--Source: https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/example-resolving-domain-codes-to-description-values.htm
select
extractvalue(codedvalues.column_value,'CodedValue/Code') as domain_code,
extractvalue(codedvalues.column_value,'CodedValue/Name') as domain_description
from
sde.gdb_items_vw items
cross join
xmlsequence(xmltype(definition).extract('/GPCodedValueDomain2/CodedValues/CodedValue')) codedvalues
where
items.name is not null

Time: 10 seconds. 

 

Select domain codes/descriptions using XMLTABLE (the new way):

select      
    i.name as domain_name,
    x.code,
    x.description
from        
    sde.gdb_items_vw i
cross apply xmltable(
    '/GPCodedValueDomain2/CodedValues/CodedValue' 
    passing xmltype(i.definition)
    columns
        code        varchar2(255) path './Code',
        description varchar2(255) path './Name'
    ) x    
where      
     i.name is not null

Time: 5 seconds (twice as fast). 


Idea:

Since EXTRACTVALUE was deprecated in ~2009, and since XMLTABLE is twice as fast, could the docs be updated to use XMLTABLE?

 

Related: Oracle Database XMLTable vs ExtractValue performance
"...The SQL execution time was 4.5 times faster after switching to XMLTABLE."