SQL Select to get a subtype value from an ARC Database

Anonymous User — Fri, 22 Jun 2018 10:32:27 GMT

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

Re: SQL Select to get a subtrpe value from an ARC Database

Anonymous User — Fri, 22 Jun 2018 10:35:42 GMT

Re: SQL Select to get a subtype value from an ARC Database

RexRobichaux2 — Fri, 22 Jun 2018 13:22:10 GMT

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

topic Re: SQL Select to get a subtrpe value from an ARC Database in Geodatabase Questions

SQL Select to get a subtype value from an ARC Database

Re: SQL Select to get a subtrpe value from an ARC Database

Re: SQL Select to get a subtype value from an ARC Database