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 
    i.name as object_name,
    x.subtype_code,
    x.subtype_description,
    x.subtype_field,
    x.subtype_domain
from   
    sde.gdb_items_vw i
cross apply xmltable(
    --Hardcoded to the subtype's ACTIVITY field. Alternatively, it can be done in the WHERE clause too: WHERE X.SUBTYPE_FIELD='ACTIVITY'.  Optional: remove [FieldName="ACTIVITY"] if hardcoding to get a specific field isn't wanted.
    --'/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_domain      varchar2(255) path './DomainName'
    ) x
where  
    i.name is not null
    and i.name = 'INFRASTR.LC_EVENTS' --Hardcoded to a specific table.
--)

Result:

OBJECT_NAME         SUBT_CODE  SUBT_DESCRIPTION           SUBT_FIELD  SUBT_DOMAIN
------------------  ---------  -------------------------  ----------  ---------------
INFRASTR.LC_EVENTS          0  ACTIVE TRANSPORTATION      ACTIVITY    ACTIVITY_ATN  
INFRASTR.LC_EVENTS          1  GEODETIC CONTROL SURVEY M  ACTIVITY    ACTIVITY_GCSM 
INFRASTR.LC_EVENTS          2  MUNICIPAL STRUCTURES       ACTIVITY    ACTIVITY_MS   
INFRASTR.LC_EVENTS          3  ROADS                      ACTIVITY    ACTIVITY_RD   
INFRASTR.LC_EVENTS          4  STREETSCAPE & STREETLIGHT  ACTIVITY    ACTIVITY_SS_SL
INFRASTR.LC_EVENTS          5  WATERCOURSE                ACTIVITY    ACTIVITY_WAT_RES
INFRASTR.LC_EVENTS          6  STORMWATER MANAGEMENT F    ACTIVITY    ACTIVITY_SWM_FAC
INFRASTR.LC_EVENTS          7  UNDERGROUND SERVICE        ACTIVITY    ACTIVITY_UND_SERV
INFRASTR.LC_EVENTS          8  BARRIER                    ACTIVITY    ACTIVITY_BARRIER
INFRASTR.LC_EVENTS          9  WATER/WASTEWATER           ACTIVITY    ACTIVITY_WM_SAN

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