SQL Select to get a subtype value from an ARC Database

202
2
06-22-2018 03:32 AM
Highlighted
New Contributor III

Hi,

we are migrating from a legacy Oracle database to a SQL database and as such need to ensure the data in both the source and target systems migrate in full.

in the target database we have various tables that hold valuetypes of items that are held as numbers rather than the full description of the type.

Q> Does anyone know where the subtype description may be held in the database as I can not seem to find them in the specific table.

in the example I will post via a screenshot I am selecting a type of valve in the valve table

Reply
0 Kudos
2 Replies
Highlighted
New Contributor III

This is the SQL Server Management studio query without the descriptionThis is the ARC Catalog view with the description

Reply
0 Kudos
Highlighted
Occasional Contributor II

Hello Chris, 

  I have this SQL query that might be relevant for what you are trying to do- if nothing else I think it will allow you to modify it depending upon if you're interested in associated domains and if domains are used in conjunction with subtypes (looks like they are from your screenshot). As you suggested- subtypes and domains are largely managed / stored within the gdb_items and gdb_itemtypes tables (if your geodatabase is dbo schema just change table owner schema from sde. to dbo.- the rest should work as is):

SELECT distinct
st.SubtypeCode,
st.SubtypeName,
st.DomainName,
dom.DomainCode,
dom.DomainValue
FROM 
(SELECT 
subtype.value('SubtypeCode[1]', 'nvarchar(max)') AS "SubtypeCode",
subtype.value('SubtypeName[1]', 'nvarchar(max)') AS "SubtypeName", 
subtype.value('descendant::DomainName[1]', 'nvarchar(max)') AS "DomainName"
FROM 
dbo.GDB_ITEMS AS items INNER JOIN dbo.GDB_ITEMTYPES AS itemtypes ON 
items.Type = itemtypes.UUID
CROSS APPLY 
items.Definition.nodes('//Subtypes/Subtype') AS Subtypes(subtype)) st INNER JOIN
(SELECT items.Name,
codedValue.value('Code[1]', 'nvarchar(max)') AS "DomainCode",
codedValue.value('Name[1]', 'nvarchar(max)') AS "DomainValue"
FROM
dbo.GDB_ITEMS AS items INNER JOIN dbo.GDB_ITEMTYPES AS itemtypes
ON items.Type = itemtypes.UUID
CROSS APPLY
items.Definition.nodes ('//CodedValues/CodedValue') AS CodedValues(codedValue)) dom ON st.DomainName = dom.Name 

Reply
0 Kudos