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 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      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:**

 - **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_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)][1]


  [1]: https://stackoverflow.com/a/72760995/5576771