2

I would like to be able to access the list of historical markers that is presented in the "Change Version" dialog in ArcMap - https://desktop.arcgis.com/en/arcmap/latest/manage-data/geodatabases/changing-versions-in-arcmap.htm

I have searched the ArcSDE system tables, but I am unable to find this information - http://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-oracle/system-tables-oracle.htm

Ideally, I would like to be able to access the name of the historical marker and other pertinent information like the creation date/time - much like the information that is stored in the GDB "VERSIONS" table for transactional versions.

3

You should be able to get everything you need from the gdb_items and gdb_itemtypes tables. The following query should get you started on SQL Server.

SELECT items.Name AS "Marker Name", 
    Definition.value('(/GPHistoricalMarker/TimeStamp/node())[1]','nvarchar(max)') AS "TimeStamp",
    CONVERT(datetime, Definition.value('(/GPHistoricalMarker/TimeStamp/node())[1]','nvarchar(max)')) AS "DateTime"
FROM dbo.GDB_ITEMS AS items INNER JOIN dbo.GDB_ITEMTYPES AS itemtypes
    ON items.Type = itemtypes.UUID
WHERE itemtypes.Name = 'Historical Marker'

More information: Query geodatabase system tables with SQL

If you are using Oracle, make sure to read the documentation. You'll need to access the GDB_ITEMS_VW view and set you the extproc.

Edit - This worked for me on ArcSDE 10.3 running on Oracle 11g:

SELECT items.name AS marker_name,
extractvalue(xmltype(items.definition), '*/TimeStamp') AS marker_timestamp_string,
to_timestamp(extractvalue(xmltype(items.definition), '*/TimeStamp'), 'YYYY-MM-DD"T"HH24:MI:SS.ff3') AS marker_timestamp_timestamp,
cast(to_timestamp(extractvalue(xmltype(items.definition), '*/TimeStamp'), 'YYYY-MM-DD"T"HH24:MI:SS.ff3') AS DATE) AS marker_timestamp_date
FROM sde.gdb_items_vw items
INNER JOIN sde.gdb_itemtypes itemtypes
ON items.type = itemtypes.uuid
WHERE itemtypes.name = 'Historical Marker'

Thanks!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.