4

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?

7

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
  • 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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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