Return to Revisions

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