Desktop Help 10.0 - 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 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.

2/5/2013