XML columns in a geodatabase in SQL Server

XML columns in a geodatabase in SQL Server
Release 9.3

Note:This topic was updated for 9.3.1.

XML is an open standard for defining data elements within documents. To store XML data in a SQL Server database, you use XML columns. XML columns require text indexing functionality to be installed and configured in the SQL Server database. See the topic Configuring a database to support XML columns for information on how to do this.

With an XML column, you can store XML documents that describe the source of a raster or a detailed feature classification. XML columns are also used to store collections of metadata documents to support ArcIMS Metadata Services.

XML columns in ArcCatalog and ArcMap

XML columns are not completely supported in the geodatabase. This means

You will not be able to preview the contents of the XML column in a table in ArcCatalog. The column will be blank or have unintelligable characters in it.

On the Fields tab of the table Properties dialog box, there will be no data type listed for the XML column.

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

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

You will not be able to register the table with an XML column as versioned.

If you open a table that contains an XML column in ArcMap, the XML column will be blank.

To edit a table with an XML column in ArcMap, you can use a nonversioned editing session. However, you won't be able to edit the XML column itself, only the other fields in the table.

You use the sdexml administrative command to create and manage XML columns, examine XML documents, and get statistics describing the collection of documents.

XML columns in a SQL Server DBMS

Three ArcSDE system tables are 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_document and SDE_xml_xpath_index tables. These tables are owned by the user who owns the business table containing the XML column.

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

NOTE: None of these tables should be altered using SQL.

SDE_xml_columns

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

Field name	Field type	Description
column_id	int	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.
registration_id	int	The identifier of the business table containing the XML column and foreign key to the SDE_table_registry system table.
column_name	nvarchar(32)	The name of the column in the business table that is the XML column.
index_id	int	The identifier of the XPath index associated with the XML column (if one exists) and foreign key to the SDE_xml_indexes table. Nulls allowed.
minimum_id	int	The value of the initial number used in the business table's XML column to identify individual XML documents. Nulls allowed.
config_keyword	nvarchar(32)	The DBTUNE configuration keyword whose parameters determine how the XML document, the XML XPath index tables, and the 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. Nulls allowed.
xflags	int	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.

SDE_xml_indexes

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

Field name	Field type	Description
index_id	int	The identifier of the XPath index and the table's primary key.
index_name	nvarchar(32)	The name of the XPath index. For XPath indexes associated with an ArcIMS Metadata Service, the name will be "ims_xml#", where # is the identifier of the XML column in the metadata service's business table.
owner	nvarchar(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.
index_type	int	A value indicating the type of XPath index. With ArcSDE 9.1 and later, the value will be 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, only the index type SE_XML_INDEX_DEFINITION is supported.
description	nvarchar(64)	Text identifying the XPath index. If an index definition file was used to create the index, the index description may be specified at the top of the file. Nulls allowed.

SDE_xml_index_tags

An XML column 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 an XML column's XPath index.

Field name	Field type	Description
index_id	int	The identifier of the XPath index associated with an XML column (if one exists) and foreign key to the SDE_xml_indexes table.
tag_id	int	The identifier of an XPath or tag.
tag_name	nvarchar(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.
data_type	int	A value indicating if the XML element or attribute will be indexed as a number, a varchar, or as text. A 1 indicates the content of the tag will be indexed as text; a 2 indicates the content of the tag will be indexed as a number; a 3 indicates the content of the tag will be indexed as a varchar.
tag_alias	int	A number that may be used to identify an XPath. For example, the Z39.50 communication protocol uses numeric codes to refer to content that may be searched. This column is not used by the ArcIMS Z39.50 Connector. Nulls allowed.
description	nvarchar(64)	Text identifying the content that should be contained in the XML element or attribute. Nulls allowed.
is_excluded	int	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.

SDE_xml_doc<column_id>

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

Field name	Field type	Description
sde_xml_id	int	The identifier for an XML document stored in the XML column and primary key for the table.
doc_property	int	A value indicating whether any conflicts were 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. NULL value = There were no problems indexing the document.
xml_doc	image	The XML document.
xml_doc_val	image	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_time_stamp	time stamp	Used to support incremental updates to the text index.

sde_xml_idx<column_id>

The sde_xml_idx<column_id> table is created for XML 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 XML column.

Field name	Field type	Description
xml_key_column	int	The identifier for the indexed value and primary key for the table
sde_xml_id	int	The identifier for the XML document that contains the indexed value
tag_id	int	The identifier for the tag associated with the XML column's XPath index, which identifies where in the document the value is stored
double_tag	float	The indexed value, when the tag is defined as DOUBLE in the XPath index definition Nulls allowed
string_tag	nvarchar(256)	The indexed value, when the tag is defined as VARCHAR in the XPath index definition Nulls allowed
text_tag	ntext	The indexed value, when the tag is defined as STRING in the XPath index definition Nulls allowed
sde_time_stamp	time stamp	Used to support incremental updates to the text index

The following is a diagram of a table with an XML column 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 system tables used to track XML columns in SQL Server

XML columns in an XML document

You cannot export a table containing an XML column to an XML Workspace Document. You can export it to an XML Recordset Document, but there is nothing within the document to distinguish the column as XML.

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