Esri has some documentation that shows how to extract the descriptions of coded value domains and subtypes with an SQL query in an Enterprise Geodatabase. These pages seem to be in the archive section of their website and not in the latest documentation. We are documenting them here in case these pages are eventually deleted as these queries are handy when creating query layers and views.
Finding subtype codes and descriptions using SQL
Link to original documentation:
Copy of Page Text:
XML column queries contain an example of extracting the values from multiple XML elements with an XPath expression in SQL Server to find the codes and values from a coded value domain. A similar approach can be used to find the subtype names and codes from a feature class. The Oracle example shows how you can query the gdb_items_vw to extract the subtype information.
For an example of how to use the “list_all_subtypes” view below, check out the following example.
Count Feature Class Attachments with SQL
SQL Server:
The following example queries a dbo-schema geodatabase (Montgomery) in SQL Server to get the subtype names and codes for a parcels feature class owned by user Jake:
SELECT subtype.value('SubtypeName[1]', 'nvarchar(max)') AS "Description",
subtype.value('SubtypeCode[1]', 'int') AS "Code"
FROM dbo.GDB_ITEMS AS items
INNER JOIN dbo.GDB_ITEMTYPES AS itemtypes
ON items.Type = itemtypes.UUID
CROSS APPLY
items.Definition.nodes('/DEFeatureClassInfo/Subtypes/Subtype') AS Subtypes(subtype)
WHERE items.Name = 'Montgomery.Jake.Parcels'
PostgreSQL:
SELECT items.name AS Name,
(xpath('//SubtypeName/text()', subtype::xml))[1]::text AS Description,
(xpath('//SubtypeCode/text()', subtype::xml))[1]::text::int AS Code
FROM sde.gdb_items AS items
INNER JOIN sde.gdb_itemtypes AS itemtypes ON items.type = itemtypes.uuid,
LATERAL unnest(xpath('/DEFeatureClassInfo/Subtypes/Subtype', items.definition::xml)) AS subtype;
Oracle:
This example queries the gdb_items_vw in Oracle to pull out the part of the string that contains the subtype code and description and finds the values for a fittings feature class owned by user Vlad.
SELECT EXTRACTVALUE(fields.column_value, '/Subtype/SubtypeName') AS Description,
EXTRACTVALUE(fields.column_value, '/Subtype/SubtypeCode') AS Code
FROM sde.gdb_items_vw,
TABLE(XMLSEQUENCE(XMLType(Definition).Extract('/DEFeatureClassInfo/Subtypes/Subtype'))) fields
WHERE Name = 'VLAD.Fittings';
Resolving domain codes to description values using SQL
Link to original documentation:
Copy of Page Text:
XML column queries include an example of how to extract the code and description pairs from a coded value domain as a result set. Beyond simple schema investigation, a practical application of this ability is resolving the codes from a dataset.
In many cases, the codes in a coded value domain are arbitrarily assigned; for example, in a coded value domain of pipe materials, the domain’s description values may be Copper, PVC, and Steel, but the domain’s codes could be 1, 2, and 3, which are of little use to users executing a SQL query on a table that uses the domain.
The following examples show how to query a coded value domain in a subquery, and then join those results to the results from querying a table that uses the domain.
In the first example, the zoning column of the parcels table (owned by user molly) uses the ZoningCodes domain. The parcels table is joined with the ZoningCodes coded value domain to return a list of the domain codes and descriptions.
SQL Server:
SELECT OBJECTID AS "Object ID", Value AS "Zoning Code"
FROM molly.parcels
LEFT OUTER JOIN
(SELECT codedValue.value('Code[1]', 'nvarchar(max)') AS "Code",
codedValue.value('Name[1]', 'nvarchar(max)') AS "Value"
FROM GDB_ITEMS AS items
INNER JOIN GDB_ITEMTYPES AS itemtypes
ON items.Type = itemtypes.UUID
CROSS APPLY items.Definition.nodes
('/GPCodedValueDomain2/CodedValues/CodedValue') AS CodedValues(codedValue)
WHERE itemtypes.Name = 'Coded Value Domain'
AND items.Name = 'ZoningCodes') AS CodedValues
ON molly.parcels.zoning = CodedValues.Code
In this example, the material column of the distribmains table uses the material domain. The distribmains table is joined with the material coded value domain to return a list of the domain codes and descriptions.
PostgreSQL:
SELECT items.name AS DomainName,
items.uuid AS DomainID,
(xpath('//Code/text()', coded_value::xml))[1]::text AS Code,
(xpath('//Name/text()', coded_value::xml))[1]::text AS Value
FROM sde.gdb_items AS items
INNER JOIN sde.gdb_itemtypes AS itemtypes ON items.type = itemtypes.uuid,
LATERAL unnest(xpath('/GPCodedValueDomain2/CodedValues/CodedValue', items.definition::xml)) AS coded_value;
Oracle:
SELECT OBJECTID AS "Object ID", Value AS "Material"
FROM DISTRIBMAINS
LEFT OUTER JOIN
(SELECT EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Code') AS Code,
EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Name') AS Value
FROM SDE.GDB_ITEMS_VW items
INNER JOIN SDE.GDB_ITEMTYPES itemtypes
ON items.Type = itemtypes.UUID,
TABLE(XMLSEQUENCE(XMLType(Definition).Extract
('/GPCodedValueDomain2/CodedValues/CodedValue'))) CodedValues
WHERE itemtypes.Name = 'Coded Value Domain'
AND items.Name = 'Material') CodedValues
ON DISTRIBMAINS.MATERIAL = CodedValues.Code;
Spatialty 