XML domains(lookups): Prevent duplicates between domains

database-designduplicationoraclexml

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 |
+------------+-------------+-----------+

I have a report query that grabs the DESCRIPTION from the ACTIVITY_COMBINED_VW view:

SELECT 
    CAPITAL_PROJECTS.PROJECT_ID
   ,ACTIVITY_COMBINED_VW.DESCRIPTION
   ,CAPITAL_PROJECTS.COST
FROM 
    USER1.CAPITAL_PROJECTS 
LEFT JOIN 
    USER1.ACTIVITY_COMBINED_VW 
    ON CAPITAL_PROJECTS.ACTIVITY = ACTIVITY_COMBINED_VW.CODE;

USER1.REPORT_VW
+------------+-------------------------------+-----------+
| PROJECT_ID |        DESCRIPTION            |   COST    |
+------------+-------------------------------+-----------+
|         01 | RECONSTRUCT EXISTING SIDEWALK | 5,000,000 |
|         02 | CONSTRUCT NEW SEWER           | 6,000,000 |
|         03 | CONSTRUCT NEW ROAD            | 7,000,000 |
|         04 | RECONSTRUCT EXISTING ROAD     | 8,000,000 |
+------------+-------------------------------+-----------+

Problem:

While the GIS application does prevent users from entering duplicate values within a single domain, the application does not prevent users from entering duplicate values between different domains.

For example, it is entirely possible that a user could accidently enter a RECON_S CODE in both the ACTIVITY_SIDEWALK domain and the ACTIVITY_SEWER domain:

              +-----------------+-------------------------------+-------------------+
              |      CODE       |          DESCRIPTION          | DOMAIN            |
              +-----------------+-------------------------------+-------------------+
              | CONSTR_S        | CONSTRUCT NEW SIDEWALK        | ACTIVITY_SIDEWALK | 
Existing ---> | RECON_S         | RECONSTRUCT EXISTING SIDEWALK | ACTIVITY_SIDEWALK | 
              +-----------------+-------------------------------+-------------------+
              | CONSTR_SEW      | CONSTRUCT NEW SEWER           | ACTIVITY_SEWER    |
New,     ---> | RECON_S         | RECONSTRUCT EXISTING SEWER    | ACTIVITY_SEWER    | 
duplicate     +-----------------+-------------------------------+-------------------+
              | CONSTR_ROAD     | CONSTRUCT NEW ROAD            | ACTIVITY_ROAD     |
              | RECON_ROAD      | RECONSTRUCT EXISTING ROAD     | ACTIVITY_ROAD     |
              +-----------------+-------------------------------+-------------------+

This scenario might seem silly or unlikely in the context of the sample data. But in reality, I have dozens of domains, each with dozens of values. So yes, it's all too easy for users to enter duplicate domain values between different domains.

And so, due to the LEFT JOIN, the REPORT_VW produces incorrect results:

+------------+-------------------------------+-----------+
| PROJECT_ID |        DESCRIPTION            |  COST     |
+------------+-------------------------------+-----------+
|         01 | RECONSTRUCT EXISTING SIDEWALK | 5,000,000 | <--Two records for project 01 
|         01 | RECONSTRUCT EXISTING SEWER    | 5,000,000 | <  (an extra 5 million dollars) 
|         02 | CONSTRUCT NEW SEWER           | 6,000,000 |              
|         03 | CONSTRUCT NEW ROAD            | 7,000,000 |
|         04 | RECONSTRUCT EXISTING ROAD     | 8,000,000 |
+------------+-------------------------------+-----------+

Question:

Is there anything I can do to prevent duplicate domain values being entered between domains?

Under normal circumstances, I think domains (lookup tables) would be stored in plain old database tables. A simple unique index or primary key would be used.

But I'm not so lucky. My domains are stored in an XML column in a system table.

Ideas?

Best Answer

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('<x1><p1>HELLO</p1><p2>WORLD</p2></x1>');

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('<x1><p1>SECOND</p1><p2>TEST</p2></x1>');
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('<x1><p1>HELLO</p1><p2>TEST</p2></x1>');
*
ERROR at line 1:
ORA-00001: unique constraint (BP.I1) violated

Related Solutions

Generate XML using Oracle XML schema

It is fairly direct to push an already existing select to XML: http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#i1028612

Creating XML is part of the basic SQL specifications and is available on most servers. You can check out SQL server's version of it here: http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/

Built-in XML functions

@elimerv,

Are you sure you are not having something else in place of "single-quote" that is expected there? Also, you don't tell what kind of data does the "pm" table has in the pm_id column (BTW, your latest SQL doesn't appear to be correct as your sample_data subquery has an "id" column whereas your main query refers to "pm_id" column). This is what I get on 11.2.0.1 First, your latest query, as it is (except for tables used):

with sample_data as (
   select '123#@#' id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
  )
 10  /
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
                                                     *
ERROR at line 8:
ORA-00904: "PM_ID": invalid identifier

Now, with correct column name used:

with sample_data as (
   select '123#@#' pm_id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
  )
 10  /

no rows selected

And, when I replaced one of the "single-quotes" with a different (but similar looking) character :

with sample_data as (
   select '123#@#' pm_id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract(`//x/*'))) t
  )
 10  /
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract(`//x/*'))) t
                                                                                                 *
ERROR at line 8:
ORA-00911: invalid character

Does this give you any idea? BTW, are you sure you need XML processing to solve your problem ?

Best Answer

Related Solutions

Generate XML using Oracle XML schema

Built-in XML functions

Related Question