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.
Extract the XML from the
DEFINITION field from
In this example, for simplicity, I've only queried non-spatial tables (not feature classes or subtypes).
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
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
12 i.NAME IS NOT NULL AND
13 EXTRACTVALUE(definition_xml.COLUMN_VALUE, 'GPFieldInfoEx/DomainName') IS NOT NULL
14 ORDER BY
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.
Get the tags:
And put them into the SQL:
LINE 10 CROSS JOIN TABLE(XMLSEQUENCE(XMLType(definition).Extract('/
LINE 3 ,EXTRACTVALUE(coded_values.COLUMN_VALUE, '
Name') AS field_name
Like I said, to the uninitiated, this is wildly unintuitive and foreign. Good luck!
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
An overview of geodatabase system tables
XML Schema of the Geodatabase
A better alternative for getting the XML path of a geodatabase object is to look at the
definition column in
- Copy and paste the
definition column text to notepad
- Save the file as a
- Open with an XML viewer such as Internet Explorer
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'