Reading Coded Domains from ArcSDE 10 RDBMS using SQL/ADO.net

Question

I need to read ArcGIS Domain Coded Value pairs (value/description) from SQL Server / Oracle in SQL (i.e. not using ArcObjects). This is an XML field. I can get the domain definition thus

select gi.definition from dbo.gdb_items gi, dbo.gdb_itemtypes git where git.Name='Coded Value Domain' and gi.Name='MAJORTOWN'

(where MAJORTOWN is my domain name)...just looking for the next, XML-parsing part.

I want an IDictionary of the value,description.

Anyone done this?

Answer 1

From the help doc on XML column queries:

-- Get the code/value pairs for each coded value domain in the geodatabase.
SELECT
 codedValue.value('Code[1]', 'nvarchar(max)') AS "Code",
 codedValue.value('Name[1]', 'nvarchar(max)') AS "Value"
FROM
 dbo.GDB_ITEMS AS items INNER JOIN dbo.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 = 'Material'
Code Value
CI Cast iron
DI Ductile iron
PVC PVC
AC Asbestos concrete
COP Copper

• Luke: so how do you do this @ 9.2 where gdb_items and gdb_itemtypes do not exist yet?
• Kirk Kuykendall: AFAIK pre 9.3 stored the cvd's in blobs, so I think you're SOL.
• Luke: I actually managed to piece together a method that worked out okay as long as the domain wasn't of type integer.

Answer 2

Here's a c# based solution for SDE 9.3. (I didn't have GDB_ITEMS and GDB_ITEMTYPES, just GDB_CODEDDOMAINS) -- Adapted from VB solution found here http://forums.esri.com/Thread.asp?c=2&f=59&t=121270

using(var conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SDE"].ToString())) 
using(var cmdRead = conn.CreateCommand())
{
 conn.Open(); 
 cmdRead.CommandText = "SELECT [CodedValues] FROM [dbo].[GDB_CODEDDOMAINS] WHERE DomainID = 4";
 try
 { 
 var data = cmdRead.ExecuteReader();
 var domainVals = new Dictionary<string, string>(); 
 while (data.Read())
 {
 Byte[] bytes = (byte[])data[0];
 var binaryRdr = new BinaryReader(new MemoryStream(bytes));
 var numPairs = binaryRdr.ReadInt32();
 Char[] separator = binaryRdr.ReadChars(2);
 var streamLen = (int)binaryRdr.BaseStream.Length;
 var streamPos = (int)binaryRdr.BaseStream.Position;
 string conjoinedVals = "";
 Char[] allChars = binaryRdr.ReadChars(streamLen - streamPos);
 foreach (var ch in allChars)
 {
 conjoinedVals += ch;
 }
 string[] values = conjoinedVals.Split(separator);
 var index = 0;
 for (var i = 0; i < numPairs; i++)
 {
 domainVals.Add(values[index], values[index + 1]);
 index += 2;
 }
 }
 return domainVals;
 }
 catch (Exception e)
 {
 return new Dictionary<string, string>();
 }
}