ArcGIS Desktop

  • Documentation
  • Support

  • My Profile
  • Help
  • Sign Out
ArcGIS Desktop

ArcGIS Online

The mapping platform for your organization

ArcGIS Desktop

A complete professional GIS

ArcGIS Enterprise

GIS in your enterprise

ArcGIS for Developers

Tools to build location-aware apps

ArcGIS Solutions

Free template maps and apps for your industry

ArcGIS Marketplace

Get apps and data for your organization

  • Documentation
  • Support
Esri
  • Sign In
user
  • My Profile
  • Sign Out

Help

  • Home
  • Get Started
  • Map
  • Analyze
  • Manage Data
  • Tools
  • More...

Example: Resolving domain codes to description values using SQL

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

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

ArcGIS Desktop

  • Home
  • Documentation
  • Support

ArcGIS Platform

  • ArcGIS Online
  • ArcGIS Desktop
  • ArcGIS Enterprise
  • ArcGIS for Developers
  • ArcGIS Solutions
  • ArcGIS Marketplace

About Esri

  • About Us
  • Careers
  • Insiders Blog
  • User Conference
  • Developer Summit
Esri
Tell us what you think.
© Copyright 2016 Environmental Systems Research Institute, Inc. | Privacy | Legal