List of all fields and their domains for each table using SQL (table name, field name, domain name)

Question

I'm trying to list all fields (in all tables in an Oracle geodatabase) and the coded value domains that they use.

+------------+------------+-------------+
| table_name | field_name | domain_name |
+------------+------------+-------------+
| table_1    | field_1    | domain_A    |
| table_1    | field_2    | null        |
| table_1    | field_3    | domain_B    |
| table_1    | field_4    | domain_B    |
+------------+------------+-------------+
| table_2    | field_1    | null        |
| table_2    | field_2    | domain_C    |
| table_2    | field_3    | domain_A    |
+------------+------------+-------------+

I've tried using the third example on this page: Query geodatabase system tables with SQL. It gets me the table names and the associated domains. But it's missing the field name:

SELECT reltypes.name AS type,
   origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM sde.gdb_items origin_items,
   sde.gdb_itemrelationships relationships,
   sde.gdb_items dest_items,
   sde.gdb_itemrelationshiptypes reltypes
WHERE
   origin_items.UUID = relationships.originid AND
   dest_items.UUID = relationships.destid AND
   relationships.type = reltypes.UUID AND
   reltypes.name = 'DomainInDataset' AND
   origin_items.name LIKE 'PUBWORKS.%'
ORDER BY  origin_items.name

How can I make a list of all fields and their domains, for each table using SQL? Keep in mind that a domain can be used by more than one field, per table (or even by multiple tables).

Environment is Oracle 12c 10.3.1 Geodatabase.

Answer 1

I'll start with a declaimer: I've cobbled this info together from various scraps of documentation that I found on various ESRI pages. I'm not a geodatabase developer or XML developer, so it's very possible that I've missed the mark here in some way.

The Solution:

Extract the XML from the DEFINITION field from SDE.GDB_ITEMS_VW.

In this example, for simplicity, I've only queried non-spatial tables (not feature classes or subtypes).

1    SELECT
2        i.NAME AS item_name
3        ,EXTRACTVALUE(definition_xml.COLUMN_VALUE, 'GPFieldInfoEx/Name') AS field_name
4        ,EXTRACTVALUE(definition_xml.COLUMN_VALUE, 'GPFieldInfoEx/DomainName') AS domain_name 
5        ,it.NAME AS item_type
6    FROM        
7         SDE.GDB_ITEMS_VW i 
8         JOIN SDE.GDB_ITEMTYPES it ON 
9             i.Type = it.UUID
10        CROSS JOIN TABLE(XMLSEQUENCE(XMLType(definition).Extract('/DETableInfo/GPFieldInfoExs/GPFieldInfoEx'))) definition_xml
11    WHERE        
12        i.NAME IS NOT NULL AND
13        EXTRACTVALUE(definition_xml.COLUMN_VALUE, 'GPFieldInfoEx/DomainName') IS NOT NULL 
14    ORDER BY
15        i.NAME

Note: Feature Classes and Subtypes can be easily added to the query, by extracting them specifically in separate queries, then UNIONing them all together.

A note about GDB tables & views in oracle:

In geodatabases in Oracle and Informix, the XML columns use ArcSDE XML, which stores information as BLOBs in a series of separate tables. As such, they cannot be directly accessed with SQL.
To allow you to view the contents of the XML columns in the system tables in geodatabases in Oracle, two system views exist that store the contents from the geodatabase system table XML columns in a CLOB data type. The GDB_Items_vw shows the contents of the Definition, Documentation, and ItemInfo columns from the GDB_Items table in CLOB columns. The GDB_ItemRelationships_vw shows the contents of the Attributes column of the GDB_ItemRelationships table in a CLOB column. The contents of the CLOB columns can be read as text. Source

The Real Challenge:

The real challenge here is learning how to find, view, and interpret XML data from XML columns; it is not intuitive.

I figured it all out by forcing myself to thoroughly understand this sample XML query. Then I did some reading about geodatabase XML (references below). Then adapted the sample query to my needs.

The over-simplified explanation is you need to find out what the XML path is (from the XML tags in an XML schema document of a table). Then insert the XML path into the various parts of the query.

Example:

Get the tags: <DETableInfo>, <GPFieldInfoExs>, <GPFieldInfoEx>, <Name>

And put them into the SQL:

LINE 10 CROSS JOIN TABLE(XMLSEQUENCE(XMLType(definition).Extract('/DETableInfo/GPFieldInfoExs/GPFieldInfoEx'))) coded_values

LINE 3 ,EXTRACTVALUE(coded_values.COLUMN_VALUE, 'GPFieldInfoEx/Name') AS field_name

Like I said, to the uninitiated, this is wildly unintuitive and foreign. Good luck!

References:

Reference #1:

Several XML columns exist in the GDB_Items and GDB_ItemRelationships tables that contain information about item schema and item relationships. One column in particular, the Definition column in the GDB_Items table, is indispensable for getting detailed information about a geodatabase. The type of XML document it contains depends on the specific item type, for example, the definition of a feature class contains information about the table's fields, domains used, subtypes, spatial reference, and controller dataset participation, among other things.

The simplest way to work with a value from an XML column is to retrieve the document from the database in its entirety and work with it locally. A simple example of this would be saving the XML document as a file and viewing it in an XML or text viewer. Developers using languages such as Java, C++, or C# may prefer to read the document into a Document Object Model (DOM). For SQL developers, database XML functions can be used to retrieve specific values from item definitions using XPath (a query language for XML documents). Source

Reference #2:

An overview of geodatabase system tables

Reference #3:

XML Schema of the Geodatabase

---

Update:

A better alternative for getting the XML path of a geodatabase object is to look at the definition column in SDE.GDB_ITEMS_VW (Oracle).

• Copy and paste the definition column text to notepad
• Save the file as a .xml file
• Open with an XML viewer such as Internet Explorer

Update #2:

Here's how to extract the text from the DEFINITION column (HUBECLOB):

select DBMS_LOB.substr(definition, 4000,     1) from SDE.GDB_ITEMS_VW where name = 'YOUROWNER.YOURFCNAME'
select DBMS_LOB.substr(definition, 4000,  4001) from SDE.GDB_ITEMS_VW where name = 'YOUROWNER.YOURFCNAME'
select DBMS_LOB.substr(definition, 4000,  8001) from SDE.GDB_ITEMS_VW where name = 'YOUROWNER.YOURFCNAME'
select DBMS_LOB.substr(definition, 4000, 12001) from SDE.GDB_ITEMS_VW where name = 'YOUROWNER.YOURFCNAME'

Answer 2

I used this syntax for SQL and it worked.

SELECT
    i.NAME AS item_name
    ,xVal.value('Name[1]', 'nvarchar(max)') field_name
    ,xVal.value('DomainName[1]', 'nvarchar(max)') domain_name 
    ,it.NAME AS item_type
FROM        
     SDE.GDB_ITEMS  i 
JOIN SDE.GDB_ITEMTYPES it 
ON 
         i.Type = it.UUID
    CROSS APPLY i.Definition.nodes('/DETableInfo/GPFieldInfoExs/GPFieldInfoEx') dx(xVal)
WHERE        
    i.NAME IS NOT NULL AND
    xVal.value('DomainName[1]', 'nvarchar(max)') IS NOT NULL 
ORDER BY
    i.NAME

Answer 3

Modified from Jaime Burgos. The CROSS APPLY line had the parameter for Oracle. It is different in SQL as included below.

SELECT
    i.Name AS FeatureClass
    ,xVal.value('Name[1]','nvarchar(max)') Field
    ,xVal.value('DomainName[1]', 'nvarchar(max)') Domain
FROM GDB_ITEMS  i JOIN GDB_ITEMTYPES it
    ON i.Type = it.UUID
CROSS APPLY i.Definition.nodes('/*/GPFieldInfoExs/GPFieldInfoEx') dx(xVal)
WHERE i.NAME IS NOT NULL
    AND xVal.value('DomainName[1]', 'nvarchar(max)') IS NOT NULL
ORDER BY i.NAME

Answer 4

This is how it can be done for subtypes.

Feature class subtypes:

select       
     i.name as table_name
    ,extractvalue(definition_xml.column_value, 'SubtypeFieldInfo/FieldName') as field_name
    ,extractvalue(definition_xml.column_value, 'SubtypeFieldInfo/DomainName') as domain_name
    ,it.name || ' [sub type]' as item_type
from        
     sde.gdb_items_vw i 
inner join 
    sde.gdb_itemtypes it 
    on i.type = it.uuid
cross join 
    table(xmlsequence(xmltype(definition).extract('/DEFeatureClassInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo'))) definition_xml
where        
      extractvalue(definition_xml.column_value, 'SubtypeFieldInfo/DomainName') is not null
      and i.name = 'YOUR_OWNER.YOUR_FEATURE_CLASS'
      --and rownum <= 1266

Non-spatial table subtypes:

select       
     i.name as table_name
    ,extractvalue(definition_xml.column_value, 'SubtypeFieldInfo/FieldName') as field_name
    ,extractvalue(definition_xml.column_value, 'SubtypeFieldInfo/DomainName') as domain_name
    ,it.name || ' [sub type]' as item_type
from        
     sde.gdb_items_vw i 
inner join 
    sde.gdb_itemtypes it 
    on i.type = it.uuid
cross join 
    table(xmlsequence(xmltype(definition).extract('/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo'))) definition_xml
where        
      extractvalue(definition_xml.column_value, 'SubtypeFieldInfo/DomainName') is not null
      and i.name = 'YOUR_OWNER.YOUR_TABLE'
      --and rownum = 50

Note: This SQL works ok for a single feature class or a single table.

But it fails on certain rows when performed on all feature classes or tables. I don't know why. There must be problems in the XML/system table.