Ejemplo: descubrir el uso del dominio mediante SQL

Las consultas de relación analizan cómo utilizar la tabla GDB_ItemRelationships para descubrir qué tablas y clases de entidad utilizan un dominio en particular para la validación. Sin embargo, es posible encontrar más información detallada, como a qué campo se aplica el dominio y qué subtipos lo utilizan (si existen subtipos). Los siguientes pasos explican resumidamente una forma de buscar esta información:

1. Inserte los nombres y las definiciones de todos los elementos con relaciones a un dominio especificado en un conjunto de registros temporales.
2. Extraiga los nombres de los campos de todos los elementos donde el dominio se utiliza en el nivel de la clase (/*/GPFieldInfoExs/GPFieldInfoEx/DomainName).
3. Extraiga los nombres de los campos y los subtipos de todos los elementos donde el dominio se utiliza en el nivel del subtipo (/*/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo/DomainName).
4. Combine estos dos conjuntos de resultados.

El siguiente ejemplo realiza una consulta a una geodatabase en SQL Server para un dominio denominado Material.

--Queries an sde-schema geodatabase in SQL Server

DECLARE @DOMAIN_NAME NVARCHAR(MAX);
SET @DOMAIN_NAME = 'Material';

DECLARE @CLASS_DEFS TABLE
(
     Name nvarchar(max),
     Definition XML
)

--Insert records to temporary record set
INSERT INTO @CLASS_DEFS
SELECT
  sde.gdb_items.Name,
  sde.gdb_items.Definition
FROM
-- Get the domain item's UUID.
((SELECT GDB_ITEMS.UUID AS UUID
  FROM sde.gdb_items INNER JOIN sde.gdb_itemtypes
  ON sde.gdb_items.Type = sde.gdb_itemtypes.UUID
  WHERE
    sde.gdb_items.Name = @DOMAIN_NAME AND
    sde.gdb_itemtypes.Name IN ('Coded Value Domain','Range Domain')) AS Domain

-- Find the relationships with the domain as the DestinationID.
INNER JOIN sde.gdb_itemrelationships
ON Domain.UUID = sde.gdb_itemrelationships.DestID)

-- Find the names of the origin items in the relationships.
INNER JOIN sde.gdb_items
ON Domain.UUID = sde.gdb_itemrelationships.DestID

-- Extract the field definitions.
SELECT
  ClassDefs.Name AS "Class Name",
  fieldDef.value('Name[1]', 'nvarchar(max)') AS "Field Name",
  NULL AS "Subtype Name"
FROM
  @CLASS_DEFS AS ClassDefs
CROSS APPLY
  Definition.nodes('/*/GPFieldInfoExs/GPFieldInfoEx') AS FieldDefs(fieldDef)
WHERE
  fieldDef.value('DomainName[1]', 'nvarchar(max)') = @DOMAIN_NAME

UNION

SELECT
  ClassDefs.Name AS "Class Name",
  fieldDef.value('FieldName[1]', 'nvarchar(max)') AS "Field Name",
  fieldDef.value('(../../SubtypeName)[1]', 'nvarchar(max)') AS "Subtype Name"
FROM
  @CLASS_DEFS AS ClassDefs
CROSS APPLY
   Definition.nodes('/*/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo') AS FieldDefs(fieldDef)
WHERE
  fieldDef.value('DomainName[1]', 'nvarchar(max)') = @DOMAIN_NAME

Este ejemplo realiza una consulta a una geodatabase en Oracle para un dominio denominado AncillaryRoleDomain:

--Queries a geodatabase in Oracle

CREATE TABLE CLASS_DEFS  
(
	name varchar2(32),
	definition XMLType
);

--Insert records to temporary record set
INSERT INTO CLASS_DEFS
SELECT
  sde.gdb_items_vw.Name,
  XMLType(sde.gdb_items_vw.Definition)
FROM(
	(
		-- Get the domain item's UUID.
		SELECT GDB_ITEMS_VW.UUID AS UUID
		FROM sde.gdb_items_vw INNER JOIN sde.gdb_itemtypes
			ON sde.gdb_items_vw.Type = sde.gdb_itemtypes.UUID
		WHERE	sde.gdb_items_vw.Name = 'AncillaryRoleDomain' AND
			sde.gdb_itemtypes.Name IN ('Coded Value Domain','Range Domain')
    	) Domain
	-- Find the relationships with the domain as the DestinationID.
	INNER JOIN sde.gdb_itemrelationships
		ON Domain.UUID = sde.gdb_itemrelationships.DestID
) 
-- Find the names of the origin items in the relationships.
INNER JOIN sde.gdb_items_vw
	ON sde.gdb_items_vw.UUID = sde.gdb_itemrelationships.OriginID;

-- Extract the field definitions.
SELECT 	CLASS_DEFS.Name AS "Class Name",
	EXTRACTVALUE(fields.Column_Value, '/GPFieldInfoEx/Name') AS "Field Name",
	null AS "Subtype Name" 
FROM CLASS_DEFS, 
	TABLE(XMLSEQUENCE(Extract(CLASS_DEFS.definition, '/*/GPFieldInfoExs/GPFieldInfoEx'))) fields
UNION
SELECT table_name AS "Class Name",
	EXTRACTVALUE(subtypes_fields.Column_value, '/SubtypeFieldInfo/FieldName') as column_name,
	subtype_name AS "Subtype Name"
FROM	(	
		SELECT 	CLASS_DEFS.Name AS table_name,
			subtypes.COLUMN_VALUE XMLVal,
			EXTRACTVALUE(subtypes.COLUMN_VALUE, '/Subtype/SubtypeName') AS subtype_name,
			EXTRACTVALUE(subtypes.COLUMN_VALUE, '/Subtype/SubtypeCode') AS subtype_value
		FROM	CLASS_DEFS, 
			TABLE(XMLSEQUENCE(Extract(CLASS_DEFS.definition, '/DEFeatureClassInfo/Subtypes/Subtype'))) subtypes
	) subtypes_fields,
	TABLE(XMLSEQUENCE(subtypes_fields.XMLVal.Extract('/Subtype/FieldInfos/SubtypeFieldInfo'))) subtypes_fields;
  
DROP TABLE CLASS_DEFS;