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

    extractvalue(codedvalues.column_value,'CodedValue/Code') as domain_code,
    extractvalue(codedvalues.column_value,'CodedValue/Name') as domain_description
    sde.gdb_items_vw items
cross join 
    xmlsequence(xmltype(definition).extract('/GPCodedValueDomain2/CodedValues/CodedValue')) codedvalues
    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


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

1 Answer 1


This seems to work:

--create or replace view d_all_coded_value_domains_vw as 
    cast(rownum as number(38,0)) as rownum_,
    i.name as domain_name
    sde.gdb_items_vw i
cross apply xmltable(
    passing xmltype(i.definition)
        code        varchar2(255) path './Code',
        description varchar2(255) path './Name'
    ) x    
    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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.