--create or replace view sub_lc_events_asset_class_activity_vw as (
select
ix.name as object_namesubtype_code,
x.subtype_codesubtype_description,
x.subtype_descriptionsubtype_field,
x.subtype_fieldsubtype_field_domain,
xi.subtype_domainname 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_domain 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.
--)
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 and ACTIVITY_UND_SERV
INFRASTRx.LC_EVENTS 8 BARRIER ACTIVITY subtype_field = ACTIVITY_BARRIER'ACTIVITY'
INFRASTR.LC_EVENTS 9 WATER/WASTEWATER ACTIVITY ACTIVITY_WM_SAN--)
Result:
- 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 assde.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)