Several XML columns exist in the GDB_Items and GDB_ItemRelationships geodatabase system tables that contain information about item schema and item relationships. One column in particular—the Definition column in the GDB_Items table—provides detailed information about a geodatabase. The type of XML document the column contains depends on the specific item type. For example, the definition of a feature class contains information about the table's fields, domains and subtypes applied, the spatial reference of the geometry, and whether the feature class participates in a controller dataset.
To work with a value from an XML column in a system table in IBM Db2, Microsoft SQL Server, or PostgreSQL, retrieve the XML document from the database in its entirety and work with it locally 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). SQL developers can use database XML functions to retrieve specific values from item definitions using XPath (a query language for XML documents).
In geodatabases in Oracle and IBM Informix, the XML columns use ArcSDE XML, which stores information as BLOBs in a series of separate tables. As such, they cannot be directly accessed with SQL.
To allow you to view the contents of the XML columns in the system tables in geodatabases in Oracle, two system views exist that store the contents from the geodatabase system table XML columns in a CLOB data type. The GDB_Items_vw shows the contents of the Definition, Documentation, and ItemInfo columns from the GDB_Items table in CLOBs. The GDB_ItemRelationships_vw shows the contents of the Attributes column of the GDB_ItemRelationships table in a CLOB. CLOBs can be read as text.
The following example shows the item definition of a range domain definition in an XML document:
<? 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>
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 in a geodatabase in SQL Server:
--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
The previous example is relatively simple. For more complex solutions, consult the white paper XML Schema of the Geodatabase—particularly the appendix targeted to developers working with system tables.
You can query the other XML columns in the system tables 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 varies 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.
Extract 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