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

Bud — Sat, 09 Jul 2022 12:37:54 GMT

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."

idea Update Oracle GDB docs: EXTRACTVALUE is deprecated, use XMLTABLE instead (faster) in Data Management Ideas

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