XML domains(lookups): Prevent duplicates between domains

Background:

I have some GIS domains (a.k.a. lookup tables) that are stored in an XML column in a single system table. The domains are not directly accessible via SQL, so I use a view to extract the values. I combine the domains into a single view so that I can use them in a query for reporting.

SELECT
    SUBSTR(EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Code'),1,255) AS Code
    ,SUBSTR(EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Name'),1,255) AS Description
    ,I.NAME as Domain_Name
FROM
    SDE.GDB_ITEMS_VW I
JOIN 
    SDE.GDB_ITEMTYPES IT
    ON I.Type = IT.UUID,
    TABLE(XMLSEQUENCE(XMLType(Definition).Extract('/GPCodedValueDomain2/CodedValues/CodedValue'))) CodedValues
WHERE
    I.NAME IN('ACTIVITY_SIDEWALK'
             ,'ACTIVITY_SEWER'
             ,'ACTIVITY_ROAD')

USER1.ACTIVITY_COMBINED_VW
+-----------------+-------------------------------+-------------------+
|      CODE       |          DESCRIPTION          |      DOMAIN       |
+-----------------+-------------------------------+-------------------+
| CONSTR_S        | CONSTRUCT NEW SIDEWALK        | ACTIVITY_SIDEWALK |
| RECON_S         | RECONSTRUCT EXISTING SIDEWALK | ACTIVITY_SIDEWALK |
+-----------------+-------------------------------+-------------------+
| CONSTR_SEW      | CONSTRUCT NEW SEWER           | ACTIVITY_SEWER    |
+-----------------+-------------------------------+-------------------+
| CONSTR_ROAD     | CONSTRUCT NEW ROAD            | ACTIVITY_ROAD     |
| RECON_ROAD      | RECONSTRUCT EXISTING ROAD     | ACTIVITY_ROAD     |
+-----------------+-------------------------------+-------------------+

The report is based on the CAPITAL_PROJECTS table:

```
+------------+-------------+-----------+
| PROJECT_ID | ACTIVITY | COST |
+------------+-------------+-----------+
| 01 | RECON_S | 5,000,000 |
| 02 | CONSTR_SEW | 6,000,000 |
| 03 | CONSTR_ROAD | 7,000,000 |
| 04 | RECON_ROAD | 8,000,000 |
+------------+-------

You can create a unique function-based index on the CODE column.

create table t1 (c1 clob);

Because your table uses CLOB based on the linked documentation.

insert into t1 values('HELLOWORLD');

1 row inserted.

commit;

Commit complete.

select extractvalue(xmltype(c1), 'x1/p1') from t1;

EXTRACTVALUE(XMLTYPE(C1),'X1/P1')
---------------------------------
HELLO

Create the unique index for x1/p1:

create unique index i1 on t1(extractvalue(xmltype(c1), 'x1/p1'));

insert into t1 values('SECONDTEST');
commit;

1 row inserted.

commit;

Commit complete.

select extractvalue(xmltype(c1), 'x1/p1') from t1;

EXTRACTVALUE(XMLTYPE(C1),'X1/P1')
---------------------------------
HELLO
SECOND

Try to insert duplicate (p1=HELLO) data:

insert into t1 values('HELLOTEST');
*
ERROR at line 1:
ORA-00001: unique constraint (BP.I1) violated

StackExchange Database Administrators Q#170972, answer score: 3