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

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>();
    }
}