Re-order Attribute Domain Values

ThomasColson · ‎12-31-2014

Have you every been exceedingly annoyed by attribute domain values that aren't alphabetized or in some specific order? One could argue that it's your fault for not creating the domain values in the right order when you made it but.....

There IS a way you can update the domain values to render to end users in any way you want!

Lets say you have a domain titled "District". To see what it looks like in XML:

Select DEFINITION FROM [dbo].[GDB_ITEMS]
WHERE NAME = 'District'

And click on the value in the definition field in the results.

<GPCodedValueDomain2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:typens="http://www.esri.com/schemas/ArcGIS/10.0" xsi:type="typens:GPCodedValueDomain2">
  <DomainName>District</DomainName>
  <FieldType>esriFieldTypeString</FieldType>
  <MergePolicy>esriMPTDefaultValue</MergePolicy>
  <SplitPolicy>esriSPTDefaultValue</SplitPolicy>
  <Description>Desc</Description>
  <Owner>DBO</Owner>
  <CodedValues xsi:type="typens:ArrayOfCodedValue">
    <CodedValue xsi:type="typens:CodedValue">
      <Name>North District</Name>
      <Code xsi:type="xs:string">ND</Code>
    </CodedValue>
    <CodedValue xsi:type="typens:CodedValue">
      <Name>South District</Name>
      <Code xsi:type="xs:string">SD</Code>
    </CodedValue>
    <CodedValue xsi:type="typens:CodedValue">
      <Name>Cades Cove District</Name>
      <Code xsi:type="xs:string">CC</Code>
    </CodedValue>
  </CodedValues>
</GPCodedValueDomain2>

What I want to see is the values and codes sorted alphabetically, which they are not. First, a little Excel magic. Using the Domain to Table tool, I've exported the Domain out to an Excel Spreadsheet. Then performed my sort.

Then some field calculations. Here I'm using plain text for the XML element and cell references for the CODE and VALUE.

="<CodedValue xsi:type=""typens:CodedValue""><Name>"&B2&"</Name><Code xsi:type=""xs:string"">"&A2&"</Code></CodedValue>"

Note how the formula is all one line with no carriage returns. Otherwise when you paste the result, quotes will be added where you don't want them!

Now update the XML definition by pasting the Excel formula results to replace the XML elements (ONLY!):

UPDATE [dbo].[GDB_ITEMS]
SET DEFINITION = 
'
<GPCodedValueDomain2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:typens="http://www.esri.com/schemas/ArcGIS/10.0" xsi:type="typens:GPCodedValueDomain2">
  <DomainName>District</DomainName>
  <FieldType>esriFieldTypeString</FieldType>
  <MergePolicy>esriMPTDefaultValue</MergePolicy>
  <SplitPolicy>esriSPTDefaultValue</SplitPolicy>
  <Description>Desc</Description>
  <Owner>DBO</Owner>
  <CodedValues xsi:type="typens:ArrayOfCodedValue">
<CodedValue xsi:type="typens:CodedValue"><Name>Cades Cove District</Name><Code xsi:type="xs:string">CC</Code></CodedValue>
<CodedValue xsi:type="typens:CodedValue"><Name>North District</Name><Code xsi:type="xs:string">ND</Code></CodedValue>
<CodedValue xsi:type="typens:CodedValue"><Name>South District</Name><Code xsi:type="xs:string">SD</Code></CodedValue>
  </CodedValues>
</GPCodedValueDomain2>
'
WHERE NAME = 'District'

I have found this very handy for large domains that have dozens or hundreds of domain values that I either wanted sorted alphabetically or where I needed to make some sort of global update such as converting the text in "Description" to Camel Case.

Combining Excel with an advanced text editor such as Notepad ++ with the TextFX tools plugin can result is some very efficient management of attribute domains!

This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.