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 ACTIVITY field in the subtype. 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:**

 - The difference between the XML path of the non-spatial table vs. the feature class is in this part: `/DETableInfo` vs. `/DEFeatureClassInfo`
 - The string literals are case-sensitive. Examples: `'CodedValue/Code', '/GPCodedValueDomain2/CodedValues/CodedValue', `and `'ExampleDomainName'`
 - The performance of the views could likely be improved by creating a **materialized view** on `sde.gdb_items_vw` (and basing the subtype views on the materialized view).
 - It is also possible to extract a domain's codes and descriptions using similar logic (but that's not included in this answer)

----------

**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 |
|*  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"(+))
```

[![enter image description here][1]][1]


  [1]: https://i.sstatic.net/l2q9V.png