Select domain codes/descriptions using XMLTABLE instead of EXTRACTVALUE

Question

Oracle 18c 10.7.1 GDB:

---

The ArcGIS system table docs have an example for extracting domain codes and descriptions from XML:

Example: Resolving domain codes to description values using SQL

--simplified:
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

That example uses the EXTRACTVALUE function to get domain codes and descriptions.

But when I look the Oracle docs, they say this about EXTRACTVALUE:

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.

So instead of using EXTRACTVALUE, I want to use XMLTABLE, since that's what Oracle recommends. And also because XMLTABLE is said to be significantly faster: Oracle Database XMLTable vs ExtractValue performance

---

Question:

How can I use XMLTABLE to get domain codes and descriptions from GDB_ITEMS_VW?

Answer 1

This seems to work:

--create or replace view d_all_coded_value_domains_vw as 
select      
    cast(rownum as number(38,0)) as rownum_,
    x.code,
    x.description,
    i.name as domain_name
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 --https://gis.stackexchange.com/questions/434701/find-problem-domains-in-gdb-items-vw

Performance: I did a comparison between the old EXTRACTVALUE and the new XMLTABLE queries. XMLTABLE was twice as fast as EXTRACTVALUE (10 seconds vs. 5 seconds). So XMLTABLE is a clear winner.