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

---

enter image description here

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:

enter image description here

I wasn't referring to the true Subtype Field:

enter image description here

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:

enter image description here

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)