Return to Revisions

My first thought was to use the EXTRACTVALUE function. But that function has been deprecated by Oracle. So I'll use XMLTABLE instead:

Use XMLTABLE with the XPATH /DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"] to filter to only get the ACTIVITY values and then go back up the hierarchy to get the sub-type code and name:

--create or replace view sub_lc_events_asset_class_activity_vw as (
select 
    x.subtype_code,
    x.subtype_description,
    x.subtype_field,
    x.subtype_field_domain,
    i.name as table_name
from   
    sde.gdb_items_vw i
cross apply xmltable(
    --Hardcoded to the subtype's ACTIVITY field. Alternatively, the hardcoding can be done in the WHERE clause: WHERE X.SUBTYPE_FIELD='ACTIVITY'. Optional: remove [FieldName="ACTIVITY"] if we don't want to hardcode to get a specific field.
    --'/DEFeatureClassInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]' --Hardcoded to only get **feature class** subtypes via /DEFeatureClassInfo/.
    '/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]' --Hardcoded to only get non-spatial **table** subtypes via /DETableInfo/.
    passing xmltype(i.definition)
    columns
        subtype_code         number(38,0)  path './../../SubtypeCode',
        subtype_description  varchar2(255) path './../../SubtypeName',
        subtype_field        varchar2(255) path './FieldName',
        subtype_field_domain varchar2(255) path './DomainName'
    ) x
where  
    i.name is not null
    and i.name = 'INFRASTR.LC_EVENTS' --Hardcoded to a specific table.
    --and x.subtype_field = 'ACTIVITY'
--)

Result:

Notes:

• This query is only designed to get subtypes for non-spatial tables. To get subtypes for feature classes, change /DETableInfo to /DEFeatureClassInfo.
• The string literals are case-sensitive. Example: ./DomainName works, but ./domainname wouldn't work. It wouldn't throw an error, but the values would be null.
• The performance of the views could likely be improved by creating a materialized view on sde.gdb_items that uses the same logic as sde.gdb_items_vw (and basing the subtype query on the materialized view).
• It's also possible to extract other XML objects (like a domain's codes and descriptions) using similar logic. But that's not included in this answer.
• Source: Extract specific value from XML array (where FieldName = x)