Example: Finding the geodatabase release using SQL

You can extract the release number values from a specific XML document using an XPath expression to discover the release of the geodatabase.

Release numbers are stored as major, minor, and bug fix versions. The major version corresponds to the major release of the geodatabase. ArcGIS 8 was the first release to include ArcGIS geodatabase functionality; therefore, ArcGIS 8 corresponds to major version 1. ArcGIS 9 corresponds to major version 2, and ArcGIS 10 corresponds to major version 3.

The minor version is signified by the number to the right of the dot (.) in the ArcGIS release. For example, the minor version number for 9.3 was 3.

The bug fix version only increases if you have installed a service pack, patch, or hot fix that resulted in changes to the geodatabase schema. In general, such changes are avoided in service packs, patches, and hot fixes, so the bug fix version is usually 0.

The following queries retrieve the major, minor, and bug fix version numbers of a geodatabase:

-- Queries a dbo-schema geodatabase in SQL Server
-- Gets the geodatabase release from the workspace catalog item.

SELECT
  Definition.value('(/DEWorkspace/MajorVersion)[1]', 'smallint') AS "Major version",
  Definition.value('(/DEWorkspace/MinorVersion)[1]', 'smallint') AS "Minor version",
  Definition.value('(/DEWorkspace/BugfixVersion)[1]', 'smallint') AS "Bug fix version"
FROM
 dbo.gdb_items AS items INNER JOIN
  (SELECT UUID 
   FROM dbo.gdb_itemtypes
   WHERE Name = 'Workspace') AS itemtypes
  ON items.Type = itemtypes.UUID

-- Queries PostgreSQL
-- Gets the geodatabase release from the workspace catalog item.

SELECT
  (xpath('//MajorVersion/text()',definition))::text as "Major version",
  (xpath('//MinorVersion/text()',definition))::text as "Minor version",
  (xpath('//BugfixVersion/text()',definition))::text as "Bug fix version"
FROM
 sde.gdb_items AS items INNER JOIN
  (SELECT uuid 
   FROM sde.gdb_itemtypes
   WHERE name = 'Workspace') AS itemtypes
  ON items.type = itemtypes.uuid;

-- Queries Oracle
-- Gets the geodatabase release from the workspace catalog item.

SELECT
  EXTRACTVALUE(XMLType(Definition), '/DEWorkspace/MajorVersion') AS "Major version",
  EXTRACTVALUE(XMLType(Definition), '/DEWorkspace/MinorVersion') AS "Minor version",
  EXTRACTVALUE(XMLType(Definition), '/DEWorkspace/BugfixVersion') AS "Bug fix version"
FROM
 sde.gdb_items_vw items INNER JOIN
  (SELECT UUID 
   FROM sde.gdb_itemtypes
   WHERE Name = 'Workspace') itemtypes
  ON items.Type = itemtypes.UUID;