Normally, I'd use the EXTRACTVALUE function. But that function has been deprecated by Oracle. So I'll use XMLTABLE:
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
b.subtype_code,
b.subtype_description,
b.subtype_field,
b.subtype_domain
from
sde.gdb_items_vw a
cross apply xmltable(
'/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]' --hardcoded to the ACTIVITY field in the subtype
passing xmltype(a.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'
) b
where
a.name = 'INFRASTR.LC_EVENTS' --hardcoded
--)
Result:
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
Explain plan:
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8205 | 9158K| 32 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 8205 | 9158K| 32 (0)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 1135 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| GDB_ITEMS | 1 | 48 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | ITEMS_NAME_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | SDE_XML_DOC1 | 1 | 1087 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------
|* 6 | INDEX UNIQUE SCAN | XML_DOC1_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | XMLTABLE EVALUATION | | | | | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("GDB_ITEMS"."NAME"=U'INFRASTR.LC_EVENTS')
6 - access("GDB_ITEMS"."DEFINITION"="D1"."SDE_XML_ID"(+))
