XML column queries
Several XML columns exist in the GDB_Items and GDB_ItemRelationships tables that contain information about item schema and item relationships. One column in particular, the Definition column in the GDB_Items table, is indispensable for getting detailed information about a geodatabase. The type of XML document it contains depends on the specific item type, for example, the definition of a feature class contains information about the table's fields, domains used, subtypes, spatial reference, and controller dataset participation, among other things.
The simplest way to work with a value from an XML column is to retrieve the document from the database in its entirety and work with it locally. A simple example of this would be saving the XML document as a file and viewing it in an XML or text viewer. Developers using languages such as Java, C++, or C# may prefer to read the document into a Document Object Model (DOM). For SQL developers, database XML functions can be used to retrieve specific values from item definitions using XPath (a query language for XML documents).
Note:The signatures and behavior of XML functions vary greatly between database management systems.
A simple example of an item definition is that of a range domain. The XML document below is a typical range domain definition:
<? xml version = "1.0" encoding="utf-8"?>
<GPRangeDomain2
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xmlns:xs = "http://www.w3.org/2001/XMLSchema"
xmlns:typens = "http://www.esri.com/schemas/ArcGIS/10.0"
xsi:type = "typens:GPRangeDomain2">
<DomainName>Angle</DomainName>
<FieldType>esriFieldTypeInteger</FieldType>
<MergePolicy>esriMPTDefaultValue</MergePolicy>
<SplitPolicy>esriSPTDuplicate</SplitPolicy>
<Description>Valid rotation angles</Description>
<Owner>harley</Owner>
<MaxValue xsi:type = "xs:int">359</MaxValue>
<MinValue xsi:type = "xs:int">0</MinValue>
</GPRangeDomain2>
Generally speaking, the two most important values for a range domain are the minimum and maximum values. The XPath expressions representing these elements are /GPRangeDomain2/MinValue and /GPRangeDomain2/MaxValue, respectively. This SQL query shows how to extract these values for a specific range domain:
--Queries an sde-schema geodatabase in SQL Server
SELECT
Definition.value('(/GPRangeDomain2/MinValue)[1]','nvarchar(max)') AS "MinValue",
Definition.value('(/GPRangeDomain2/MaxValue)[1]','nvarchar(max)') AS "MaxValue"
FROM
sde.GDB_ITEMS INNER JOIN sde.GDB_ITEMTYPES
ON sde.GDB_ITEMS.Type = sde.GDB_ITEMTYPES.UUID
WHERE
sde.GDB_ITEMS.Name = 'Angle' AND
sde.GDB_ITEMTYPES.Name = 'Range Domain'
MinValue MaxValue
0 359
Given a simple example like the previous one, it is easy to find the XPaths of the information you want to return. However, for more complex solutions, consult the white paper XML Schema of the Geodatabase for XPath definitions—particularly the appendix targeted to developers working with system tables.
The other XML columns in the system tables can be queried the same way as the Definition column of the GDB_Items table, but be aware that there is no geodatabase-defined XML schema for the Documentation column. The Documentation column stores the metadata associated with geodatabase items. However, the exact set of metadata elements it contains will vary between organizations based on the metadata standard they follow and their workflow for managing the information. An XML DTD describing the structure of ArcGIS metadata—ArcGISmetadatav1.dtd—is provided with ArcGIS for Desktop in the \Metadata\Translator\Rules subfolder of the ArcGIS installation directory.
Extracting multiple values from an XML column
There are many cases for which it makes sense to extract multiple values from a single XML document. The following is the Definition value for one such example, a coded value domain:
<? xml version = "1.0" encoding="utf-8"?>
<GPCodedValueDomain2
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xmlns:xs = "http://www.w3.org/2001/XMLSchema"
xmlns:typens = "http://www.esri.com/schemas/ArcGIS/10.0"
<DomainName>Material</DomainName>
<FieldType>esriFieldTypeString</FieldType>
<MergePolicy>esriMPTDefaultValue</MergePolicy>
<SplitPolicy>esriSPTDuplicate</SplitPolicy>
<Description>Valid pipe materials</Description>
<Owner>aelflad</Owner>
<CodedValues xsi:type= "typens:ArrayOfCodedValue">
<CodedValue xsi:type= "typens:CodedValue">
<Name>Cast iron</Name>
<Code xsi:type= "xs:string">CI</Code>
</CodedValue>
<CodedValue xsi:type= "typens:CodedValue">
<Name>Ductile iron</Name>
<Code xsi:type= "xs:string">DI</Code>
</CodedValue>
<CodedValue xsi:type= "typens:CodedValue">
<Name>PVC</Name>
<Code xsi:type= "xs:string">PVC</Code>
</CodedValue>
<CodedValue xsi:type= "typens:CodedValue">
<Name>Asbestos concrete</Name>
<Code xsi:type= "xs:string">AC</Code>
</CodedValue>
<CodedValue xsi:type= "typens:CodedValue">
<Name>Copper</Name>
<Code xsi:type= "xs:string">COP</Code>
</CodedValue>
</CodedValues>
</GPCodedValueDomain2>
The values that are usually of most interest to developers and administrators are the code and value pairs, which have an XPath expression of /GPCodedValueDomain2/CodedValues/CodedValue. The following example shows how to extract multiple values from a single XML definition to get the code and value pairs for all domains in a geodatabase in SQL Server:
-- 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
For information on the system tables and views used in Oracle, see the "XML in geodatabase system tables" section of A quick tour of the geodatabase system tables.