This is what I came up with for Oracle. I used a view for the first part, to make the transition out of the way domain values are stored in XML in a Clob field instead of a XML datatype. This simplified the ultimate query. Am sure others will come up with more cool way of doing this, but this got my question answered. Suggest running this on the database server if your domain list is very large.
I have 3 items, a view, putting 5 tables in KEEP POOL that is already set up in Oracle (for speed), and a query .
1-The view needs to be created in SDE owner (or other "user geodatabase"), (one-time). It is based on the SDE.GDB_ITEMS_VW that is already installed in sde schema. The view and query assume you are logged into the database as the user that owns the SDE system table schema...so if a "user geodatabase" logon to that user schema.
2-As a DBA account, run these 5 commands. These are a one-time thing. They helped with speed. alter table sde.gdb_items storage (BUFFER_POOL KEEP); alter table sde.gdb_itemtypes storage (BUFFER_POOL KEEP); alter table sde.sde_xml_doc1 storage (BUFFER_POOL KEEP); alter table sde.sde_xml_doc2 storage (BUFFER_POOL KEEP); alter table sde.sde_xml_doc3 storage (BUFFER_POOL KEEP);
3-The query is setup as follows, it has two lines commented out with --, and WHERE clause is there in case you want to restrict by whatever, such as an objectid, or a domain owner, or domain, etc. ***I recommend you uncomment those lines and try a small query***...sample had GEOHOME as the data owner. Another option for restricting to a single domain name is just to put a WHERE clause like this WHERE objectid=321 you can do a quick check of the objected you want in the view.
--begin View
CREATE OR REPLACE FORCE VIEW SDE.DOMAIN_NEWXMLTYPE ( OBJECTID, NAME, DOMAINXML ) AS SELECT objectid, name, EXTRACT (xmltype (definition), '/GPCodedValueDomain2') domainXML FROM sde.gdb_items_vw items WHERE items.objectid IN (SELECT items.objectid FROM SDE.GDB_ITEMS items INNER JOIN SDE.GDB_ITEMTYPES itemtypes ON items.TYPE = itemtypes.UUID WHERE itemtypes.Name = 'Coded Value Domain') ; --end of view
--QUERY to run in SQL*Plus or other tool for Oracle set serveroutput off set echo off set linesize 1000 set pagesize 60 set feedback off set termout off
COLUMN D_Owner FORMAT A25 COLUMN DomainName FORMAT A25 COLUMN Description FORMAT A50 COLUMN FieldType FORMAT A30 COLUMN MergePolicy FORMAT A30 COLUMN Splitpolicy FORMAT A30 COLUMN Splitpolicy FORMAT A30 COLUMN code FORMAT A50 COLUMN name FORMAT A50
It sounds like you don't have a question with the queries, but are posting for informational purposes. There is also more info in the 10.1 doc with examples for SQLServer and Oracle. These are just a couple examples:
