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  CREAT 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;

7/11/2012