XML documents in a geodatabase stored in PostgreSQL

XML documents in a geodatabase stored in PostgreSQL
Release 9.3

XML is an open standard for defining data elements within documents. PostgreSQL does not have an XML data type, so XML documents are stored in text fields. To index all contents of the XML documents in the text fields, you need to install the TSearch2 tool. See the topic Configuring a PostgreSQL database to support XML columns for information on using this tool.

XML documents describe the source of a raster or a detailed feature classification. They also store collections of metadata documents to support ArcIMS metadata services.

XML documents in ArcCatalog and ArcMap

XML documents are not completely supported in the geodatabase. This means the following:

You cannot preview the contents of the column containing the XML document in a table in ArcCatalog. The column will be blank or have unintelligible characters in it.

On the Fields tab of the table Properties dialog box, no data type is listed for the column.

You cannot copy and paste a table containing a text column containing an XML document from one geodatabase to another.

If you import or export the table using geoproccessing tools, you have to exclude the column containing the XML document.

You cannot register as versioned the table containing an XML document.

If you open a table that contains an XML document in ArcMap, the column containing the XML document is blank.

To edit a table with a text column containing an XML document in ArcMap, you can use a nonversioned editing session. However, you can't edit the column with the XML document itself, only the other fields in the table.

Use the sdexml administrative command to examine XML documents and get statistics describing the collection of documents.

Columns containing XML documents in a PostgreSQL DBMS

Three ArcSDE system tables are used to manage XML documents: sde_xml_columns, sde_xml_index_tags, and sde_xml_indexes. These tables are owned by the ArcSDE administrator user. ArcSDE also creates two additional tables for each XML column that are used to store and index XML documents: the sde_xml_doc<column_id> and sde_xml_idx_<column_id> tables. These tables are owned by the user who owns the business table containing the XML document.

ArcSDE creates the following tables, which are used to store and index the XML documents.

NOTE: None of these tables should be altered using Structured Query Language (SQL).

sde_xml_columns

When you add a column to a business table to store XML documents, a row is added to the XML columns table. This table occurs once in each ArcSDE database.

Field name	Field type	Description
column_id	integer	The XML column's identifier and the table's primary key. This value is assigned by ArcSDE at the time the XML column is created. NOT NULL.
registration_id	integer	The identifier of the business table containing the XML column and foreign key to the table_registry system table. NOT NULL.
column_name	varchar(32)	The name of the column in the business table that is the XML column. NOT NULL.
index_id	integer	The identifier of the XPath index associated with the XML column (if one exists) and foreign key to the sde_xml_indexes table.
minimum_id	integer	The value of the initial number used in the business table's XML column to identify individual XML documents.
config_keyword	varchar(32)	The DBTUNE configuration keyword whose parameters determine how the XML document, XML XPath index tables, and text indexes created on those tables are defined in the database. For more information on DBTUNE keywords and their parameters, see DBTUNE configuration keywords and DBTUNE configuration parameter name-configuration string pairs.
xflags	integer	A value indicating if the original documents in the XML document table are stored compressed or uncompressed. By default, documents are compressed; compressed documents provide better performance. NOT NULL.

sde_xml_indexes

This table occurs once in each ArcSDE database. It contains one row for each column that has an XPath index.

Field name	Field type	Description
index_id	integer	The identifier of the XPath index and the table's primary key. NOT NULL.
index_name	varchar(32)	The name of the XPath index. For XPath indexes associated with an ArcIMS metadata service, the name is ims_xml#, where # is the identifier of the XML column in the sde_xml_columns table. NOT NULL.
owner	varchar(32)	The database user who owns the XML column. For ArcIMS metadata services, this is the user specified in the service's ArcXML configuration file. NOT NULL.
index_type	integer	A value indicating the type of XPath index. The value is 2 for the index type SE_XML_INDEX_DEFINITION and 1 for the index type SE_XML_INDEX_TEMPLATE. For XPath indexes associated with an ArcIMS metadata service, the default index type is SE_XML_INDEX_DEFINITION. NOT NULL.
description	varchar(64)	Text identifying the XPath index. If an index definition file is used to create the index, the index description can be specified at the top of the file.

sde_xml_index_tags

A column containing an XML document may optionally have an XPath index, which lets you search the content of a specific XML element or attribute in each document. The definition of which elements and attributes are included in or excluded from each XPath index is recorded in this table.

This table occurs once in each ArcSDE database. It contains one row for each XPath associated with a column's XPath index.

Field name	Field type	Description
index_id	integer	The identifier of the XPath index associated with an XML column (if one exists) and foreign key to the sde_xml_indexes table. NOT NULL.
tag_id	integer	The identifier of an XPath or tag. NOT NULL.
tag_name	varchar(1024)	An absolute XPath identifying an XML element or attribute that may occur in an XML document. For example, /metadata/mdDateSt identifies an XML element and /metadata/dataIdInfo/tpCat/TopicCatCd/@value identifies an XML attribute. These XPaths must not contain asterisks () to refer to a group of XML elements or attributes—each element or attribute is matched exactly using the XPaths specified in this table. Elements can't be named "" in a valid XML document. NOT NULL.
data_type	integer	A value indicating if the XML element or attribute is indexed as a number, varchar, or as text; a value of 1 indicates the content of the tag will be indexed as text, a 2 indicates the content of the tag is indexed as a number, and a 3 indicates the content of the tag is indexed as a varchar. NOT NULL.
tag_alias	integer	A number that can be used to identify an XPath. For example, the Z39.50 communication protocol uses numeric codes to refer to content that can be searched. This column is not used by the ArcIMS Z39.50 Connector.
description	varchar(64)	Text identifying the content that should be contained in the XML element or attribute.
is_excluded	integer	A value indicating whether the XML element is included in or excluded from the XPath index. 0 = The XPath is included. 1 = The XPath is excluded. NOT NULL.

sde_xml_doc<column_id>

The sde_xml_doc<column_id> table stores the XML document and maintains a full text index of the document's content. The ArcSDE database contains one of these tables for each column containing an XML document. The number in the table name is the column's identifier. This table contains one row for each XML document stored in the column.

Field name	Field type	Description
sde_xml_id	integer	The identifier for an XML document stored in the XML column and primary key for the table. NOT NULL.
doc_property	integer	A value indicating whether any conflicts are found when adding the content of an XML document to the XPath index. 1 = A conflict was found; for example, when an element is supposed to be indexed numerically but the document contains a string in that element instead.
xml_doc	bytea	The XML document. NOT NULL.
xml_doc_val	bytea	The content of the entire XML document with all XML tags and other markup removed. A text index is built on this column by default; this index is used to respond to full text queries. For ArcIMS metadata services, this index is used to respond to FULLTEXT requests.

sde_xml_idx<column_id>

The sde_xml_idx<column_id> table is created for columns that have an XPath text index. This table stores the text or number content for each XPath that is indexed.

The ID number in the table name is the internal registration number for the column containing the XML document.

Field name	Field type	Description
xml_key_column	integer	The identifier for the indexed value and primary key for the table NOT NULL
sde_xml_id	integer	The identifier for the XML document that contains the indexed value NOT NULL
tag_id	integer	The identifier for the tag associated with the XML column's XPath index, which identifies where in the document the value is stored NOT NULL
double_tag	double precision	The indexed value when the tag is defined as double precision in the XPath index definition
string_tag	varchar(256)	The indexed value when the tag is defined as varchar in the XPath index definition
text_tag	text	The indexed value when the tag is defined as STRING in the XPath index definition

Following is a diagram of a table with a column containing an XML document and the system tables used to track it. Dashed lines indicate implicit relationships; a solid line denotes explicitly defined relationships between tables.

Sites business table and associated system tables to track an XML column in PostgreSQL

Columns containing an XML document in an XML workspace document

You cannot export a table containing a column with an XML document to an XML workspace document. You can export it to an XML recordset document.

Please visit the Feedback page to comment or give suggestions on ArcGIS Server Help.
Copyright © Environmental Systems Research Institute, Inc.