Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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
share|improve this answer
    
so how do you do this @ 9.2 where gdb_items and gdb_itemtypes do not exist yet? –  Luke Oct 26 '11 at 16:14
    
AFAIK pre 9.3 stored the cvd's in blobs, so I think you're SOL. –  Kirk Kuykendall Oct 26 '11 at 16:38
    
I actually managed to piece together a method that worked out okay as long as the domain wasn't of type integer. –  Luke May 1 '12 at 18:12
add comment

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>();
    }
}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.