Example: Finding subtype codes and descriptions using SQL

XML column queries contains 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.

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'

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';

Note:

Be sure that when you supply literal values, the case matches the contents of the table.