XML columns in a geodatabase stored in PostgreSQL

XML is an open standard for defining data elements within documents. To store XML data in a PostgreSQL database, you can use ArcSDE XML columns or native PostgreSQL XML columns.

You can store user-defined XML documents in either type of XML. The XML_COLUMN_STORAGE sde_dbtune parameter controls the type of XML used. By default, this parameter is set to the native PostgreSQL XML data type.

XML columns in ArcGIS for Desktop

XML columns are not completely supported in the geodatabase. Therefore, the following are true:

Columns containing XML documents in a PostgreSQL DBMS

There are two different types of XML columns you can use: ArcSDE XML or PostgreSQL's native XML type. Which one you use in a dataset you create in your geodatabase is controlled by the XML_STORAGE parameter in the sde_dbtune table.

If you use the native PostgreSQL XML type, there are no extra geodatabase system tables to track it; it is tracked in PostgreSQL's system tables.

For ArcSDE XML, there are three geodatabase system tables used to manage XML columns: 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.

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

CautionCaution:

Do not alter any of these tables using 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 geodatabase.

Field name

Field type

Description

Null?

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 that is the XML column in the business table

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.

xflags

integer

A value indicating whether the original documents in the XML document table are stored compressed or decompressed

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

Null?

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

NOT NULL

owner

varchar(32)

The database user who owns the XML column

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.

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

Null?

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 whether the XML element or attribute is indexed as a number, varchar, or 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, 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.

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

Null?

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.

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

Null?

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.

Related Topics

8/21/2013