3

I'm trying to list all fields (in all tables in an Oracle geodatabase) and the cv 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.

  • 2
    Is there any particular reason why you wouldn't want to use arcpy for that, iterating your datasets with Python and listing field domains? – Alex Tereshenkov Nov 1 '16 at 17:10
  • Good question. I want to use the list to ultimately use PL/SQL to loop through all tables, all fields with CV domains, and validate all values against their associated domains (errors happen due to field calculations, etc.). And yes, this too can be done with python easily. I've chosen to try to do it with SQL and PL/SQL for a few reasons: 1) It is a massive learning opportunity (man, have I learned a lot about geodatabases, system tables, XML, SQL, PL/SQL). – Wilson Nov 1 '16 at 17:27
  • 2) From my experience, python/ArcPy is slow. I want to check my entire geodatabase often, make corrections, refresh views on the fly, and not wait very long to do so. I think SQL will be good for this. 3) I have been inspired by this post which says 'Your database code will always outlive your application client technology.'. I want to create a sustainable solution, that will not be forgotten. I'm hoping to store it in a VIEW in the database. – Wilson Nov 1 '16 at 17:27
4

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 tag info: <DETableInfo>, <GPFieldInfoExs>, <GPFieldInfoEx>, <Name>

And put it 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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.