Select subtype and a subtype field's domain names using SQL

Question

I have a non-spatial table with a subtype in an Oracle 18c 10.7.1 EGDB.

Using SQL, I want to select the following as columns in a query:

1. Subtype Code
2. Subtype Description
3. A specific subtype field called ACTIVITY.
   • Of course, there are multiple fields in the table/subtype. But I only want to select the information for this specific field.
4. And select the ACTIVITY field's domains (there is a different domain used for each subtype code).

---

The result would look like this:

SUBTYPE_CODE  SUBTYPE_DESCRIPTION                SUBTYPE_FIELD  SUBTYPE_DOMAIN
------------  ---------------------------------  -------------  ---------------
           0  ACTIVE TRANSPORTATION              ACTIVITY       ACTIVITY_ATN  
           1  GEODETIC CONTROL SURVEY MONUMENT   ACTIVITY       ACTIVITY_GCSM 
           2  MUNICIPAL STRUCTURES (BRIDGES)     ACTIVITY       ACTIVITY_MS   
           3  ROADS                              ACTIVITY       ACTIVITY_RD   
           4  STREETSCAPE & STREETLIGHTS         ACTIVITY       ACTIVITY_SS_SL
           5  WATERCOURSE                        ACTIVITY       ACTIVITY_WAT_RES
           6  STORMWATER MANAGEMENT FACILITY     ACTIVITY       ACTIVITY_SWM_FACILITY
           7  UNDERGROUND SERVICE                ACTIVITY       ACTIVITY_UND_SERV
           8  BARRIER                            ACTIVITY       ACTIVITY_BARRIER
           9  WATER/WASTEWATER                   ACTIVITY       ACTIVITY_WM_SAN

How can I select those subtype columns using SQL?

---

Note:

In hindsight, I realize that my terminology was misleading. When I said subtype field, I meant the fields in the Default Values and Domains: section:

I wasn't referring to the true Subtype Field:

Apologies if that's confusing. There seem to be two different concepts of Subtype Fields.

Answer 1

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)