Querying ArcGIS attribute subtypes and domains with SQL

ArcGIS geodatabases have the concept of domains, where an attribute can be restricted to a set of values, similar to a drop down list. Furthermore, geodatabases also allow an attribute to be specified as a subtype where the subtype determines which set of domains that apply to attributes. This can be used when setting up cascading drop down lists.

In an Enterprise Geodatabase, this subtype and domain metadata is stored in the GDB_ITEMS table in an XML definition. This ESRI technical article provides code on extracting domain values via SQL:

SELECT
items.Name,
codedValue.value('Code[1]', 'nvarchar(max)') Code,
codedValue.value('Name[1]', 'nvarchar(max)') Value
FROM SDE.GDB_ITEMS AS items
INNER JOIN SDE.GDB_ITEMTYPES AS itemtypes ON items.Type = itemtypes.UUID
CROSS APPLY items.Definition.nodes('/GPCodedValueDomain2/CodedValues/CodedValue') AS CodedValues(codedValue)

This works well if there are no subtypes, as you can just join your table or view against the Code column and display the Value column. To go one step further an incorporate subtypes, we need to introduce another query that extracts the domains used by a subtype (change MyTable and MyAttribute to appropriate values):

SELECT
SubtypeChildren.value('SubtypeCode[1]', 'varchar(50)') SubtypeCode,
DomainName.value('.', 'varchar(50)') DomainName
FROM GDB_ITEMS i
CROSS APPLY i.Definition.nodes('//Subtype') AS Subtype(SubtypeChildren)
CROSS APPLY SubtypeChildren.nodes('FieldInfos/SubtypeFieldInfo/FieldName[text()="MyAttribute"]/../DomainName') AS SubtypeFieldInfo(DomainName)
WHERE Name = 'MyTable'

This returns the subtype code as well as the domain name. The subtype code can then be joined against your table or view, while the domain name can be joined against the items.Name from the first query. To be able to return the cascaded domain, we need to join again on the first query. For example, using CTEs:

WITH domains AS (
SELECT
items.Name,
codedValue.value('Code[1]', 'nvarchar(max)') Code,
codedValue.value('Name[1]', 'nvarchar(max)') Value
FROM SDE.GDB_ITEMS AS items
INNER JOIN SDE.GDB_ITEMTYPES AS itemtypes ON items.Type = itemtypes.UUID
CROSS APPLY items.Definition.nodes('/GPCodedValueDomain2/CodedValues/CodedValue') AS CodedValues(codedValue)
),
subtypes AS (
SELECT
SubtypeChildren.value('SubtypeCode[1]', 'varchar(50)') SubtypeCode,
DomainName.value('.', 'varchar(50)') DomainName
FROM GDB_ITEMS i
CROSS APPLY i.Definition.nodes('//Subtype') AS Subtype(SubtypeChildren)
CROSS APPLY SubtypeChildren.nodes('FieldInfos/SubtypeFieldInfo/FieldName[text()="MyAttribute"]/../DomainName') AS SubtypeFieldInfo(DomainName)
WHERE Name = 'MyTable'
)
SELECT
m.Category CategoryCode,
d1.Value CategoryDescription,
m.MyAttribute TypeCode,
d2.Value TypeDescription
FROM MyTable m
INNER JOIN domains d1 ON d.Code = m.Category
INNER JOIN subtypes s ON s.SubtypeCode = m.Category
INNER JOIN domains d2 ON d2.Code = m.MyAttribute

The subtypes query above is limited to a single attribute as this was the use case I needed to support. To generalise it and return all fields, modify it to return the SubtypeCode, FieldName and DomainName.