sdexml
The sdexml command administers XML columns.
Usage syntax
sdexml -o add -l <table,column> [-M <mininum_id>] [-k <config_keyword>]
[-n <index_name> [-f <xml_index_def>] [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-q]
sdexml -o alter -l <table,column> [-M <mininum_id>] [-k <config_keyword>]
[-n <index_name> [-f <xml_index_def>] [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-N] [-q]
sdexml -o alter_template -f <xml_index_def> -n <template_name>
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>] [-N] [-q]
sdexml -o create_template -f <xml_index_def> -n <template_name>
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>] [-q]
sdexml -o delete -l <table,column>
[-s <server_name>]
[-i {<service> | <port#> | <direct connection>}]
[-D <database_name>]
-u <DB_user_name>
[-p <DB_user_password>] [-N] [-q]
sdexml -o delete_template -n <template_name> [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-N] [-q]
sdexml -o {describe | describe_long} [{-O <owner> | -l <table,column>}]
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>] [-q]
sdexml -o list -l <table,column> -v <sde_row_id>
[-i {<service> | <port#> | <direct connection>}]
[-s <server_name>] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-q]
sdexml -o optimize -l <table,column>
[-s <server_name>]
[-i {<service> | <port#> | <direct connection>}] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-q]
sdexml -o {stats | xi_stats} -l <table,column>
[-s <server_name>]
[-i {<service> | <port#> | <direct connection>}] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-q]
sdexml -h
sdexml -?
Operations
| Operation | Description |
| add | Adds a new XML column to an existing table |
| alter | Modifies some properties of the XML column |
| alter_template | Modifies the XML index template |
| create_template | Creates an XML index template |
| delete | Deletes an XML index column |
| delete_template | Deletes an XML index template |
| describe | Describes the properties of an XML column |
| describe_long | Describes the properties of the fields of an XML column |
| list | Lists the contents of an XML document |
| optimize | Optimizes the text index on the documents in the XML column |
| stats | Lists the XML column statistics |
| xi_stats | Lists the XML column index statistics |
Options
| Options | Description |
| -D | Database name (not supported on Oracle) |
| -f | Name of the XML index definition file |
| -h or -? | Use either of these options to see the usage and options for the command. Note: If using a C shell, use -h or "-\?". |
| -i | ArcSDE service name, port number, or direct connection information (default: esri_sde or 5151) |
| -k | Configuration keyword present in DBTUNE table (default: DEFAULTS) |
| -l | The business table and its XML
column If you are not the owner of the table, you must qualify the table name as owner.table. |
| -M | Minimum row ID |
| -n | Index name |
| -N | No verification is performed; the operation begins immediately after being invoked. |
| -o | Operation |
| -O | Owner name |
| -p | DBMS user password |
| -q | Quiet—all titles and warnings are suppressed. |
| -s | ArcSDE server host name (default: localhost) |
| -u | DBMS user name |
| -v | The value of the ArcSDE registered row ID column |
Discussion
The sdexml command administers XML columns in tables and feature classes in
ArcSDE geodatabases. XML
columns are designed to store entire XML documents, one per row, in tables
or layers managed by ArcSDE.
You can use the sdexml command to add or alter XML columns. The type of XML created—ArcSDE XML or the native DBMS XML—depends on the value set for the XML_COLUMN_STORAGE of the configuration keyword you specify with the -k option. If you do not specify the -k option, the value set for the XML_COLUMN_STORAGE parameter in the DEFAULTS configuration keyword is used. Native DBMS XML is supported in DB2 9.5, Oracle 11g, SQL Server, and PostgreSQL databases.
You can use the sdexml command to add or alter indexes on the documents stored in the columns, create and maintain XML index templates, generate statistics about XML documents and indexes, and display the contents of individual XML documents.
In most cases, you would use the sdexml command to administer and add indexes to ArcSDE XML columns. For DBMS XML columns, use the DBMS interface or commands.
ArcSDE supports multiple XML columns per table or feature class, but the XML columns must be the same type. In other words, you cannot store an ArcSDE XML column and a DBMS XML column in the same table. For definitions of XML terms and more information about XML documents, visit http://www.w3.org.
Indexing XML columns
A full-text index is always created on ArcSDE XML columns. If you are using native DBMS XML, the default creation of a full-text index is dependent on which DBMS you are using.
You also can enable searches within specific tags in XML documents by indexing the contents of those tags. This is done with either an XML index definition or an XML index template.
You can create an XML index definition specifically for tags in a single XML column. This is done by identifying the tags you want to index in an index definition file, and using that file with the add or alter operations. (The format of the index definition file is found below.) The index definition must have a name when it is created. This index name is stored in the ArcSDE system tables along with its ID number, index type, text description, and list of tags to index.
Additionally, you can use sdexml to create XML index templates and associate them with one or more XML columns. XML index templates are similar to XML index definitions, except they are stored once and used to index one or more XML columns containing similar information. XML index templates often include a list of tags commonly found in standardized XML documents. You can create your own templates using the create_template operation along with an index definition file. XML index templates are stored in the ArcSDE system tables and are associated with XML columns as needed. XML templates need not be associated with any XML columns, so you can create them before creating any XML columns that use them. Like index definitions, each index template must have a name.
Index definition file
An XML index definition file is a text file used to define the new index or index template. It contains a text description of an index or template plus a list of parameters for each tag to be indexed. Only one index or template may be defined in an index definition file.
Index parameters are specified with a series of lines beginning with keywords. Some keywords are optional. Each group of parameters for each tag must start with the ##TAG keyword and end with the keyword END. For missing tag parameters, default values are assigned. The keywords (such as DESCRIPTION) must be in capital letters. Keywords must start in the first column of the line of text. Following is the format of the file:
DESCRIPTION: <index description>
##TAG DESCRIPTION: <tag description>
LOCATION PATH: <location path>
DATA TYPE: <STRING or DOUBLE>
ALIAS: <alias>
EXCLUDED: <TRUE or FALSE>
END
{repeat from ##TAG to END for each tag}
- The first DESCRIPTION keyword in the file is a short text description of the index. Subsequent DESCRIPTION keywords are for each indexed tag. Each description can be up to 63 characters. All text on the line following the keyword is assumed to be part of the description, including any trailing white space characters. The default values for descriptions are blank.
- LOCATION PATH is the path to the tag to be indexed. This is a required parameter, and there is no default value. The path should not be in quotes. All text on the line following the keyword is assumed to be part of the location path.
- DATA TYPE is either STRING or DOUBLE, depending on whether text or numeric values are expected to be in the tag. The default value is STRING.
- ALIAS is a numeric value used as an alias for a tag. A value of zero means that there is no alias, which is the default value.
- EXCLUDED determines whether this tag will be indexed. This is useful for temporarily excluding a tag without removing its definition from the file.
Examples
Add an XML column
The add operation adds a new XML column to an existing table. You can specify a tag index when creating the column or add one later with the alter operation.
There are several variations on how to specify an index definition or index template to use with the XML column. In the first example, a native DBMS XML column, myxmlcol, is added to the table mytable using a custom configuration keyword, DBXML. No index will be created for the tags in the XML column.
sdexml -o add -l mytable,myxmlcol -k DBXML -i sde:oracle11g -s server2 -u me -p mypw
The second example creates an ArcSDE XML column, xcol, and an index definition on that column. You must specify a name for the index using the -n option.
sdexml -o add -l lookup,xcol -f index_paths.txt -n
lutbl_xcol_idx -k SDEXML
-i sde:sqlserver:george\ssinst -u me -p mypw
The third example associates an existing XML index template to be used to index the tags in an ArcSDE XML column.
sdexml -o add -l resource,descxcol -n template1 -k DEFAULTS -i sde:informix -s idsserver -u me -p mypw
Alter properties of an XML column
Use the alter operation to change the index or the minimum ID values for documents in an existing XML column.
The following example replaces the existing index definition (if any) on the XML column myxmlcol with an index template called template1. The XML index template template1 must already exist.
sdexml -o alter -l mytable,myxmlcol -n template1 -u me -p mypw
If you alter the index using an index definition file, the file must include the complete new index definition.
Modify an XML index template
Use the alter_template operation to modify an XML index template. You must provide a complete definition for the template in the index definition file. You cannot alter a template that is currently used by any XML columns.
sdexml -o alter_template -f template1_paths_new.txt -n template1 -i sde:informix -s idsserver -u me -p mypw
Create an XML index template
Creating an XML index template is very similar to creating an XML index definition for a column you are adding or altering. XML index templates must have a name, just as XML indexes do. Provide a complete definition for the template in an index definition file. This example shows the creation of template1 with the index definition file template1_paths.txt:
sdexml -o create_template -f template2_paths.txt -n template1 -i 3900 -u me -p mypw
Delete an XML index column
The delete operation drops the XML column and its index. All XML documents in the column are deleted.
sdexml -o delete -l lookup,xcol -i sde:sqlserver:george\ssinst -u me -p mypw
Delete an XML index template
The delete_template operation drops the XML index template from the ArcSDE system tables. You cannot delete a template that is currently used by any XML columns.
sdexml -o delete_template -n template2 -i 3900 -u me -p mypw
Describe an XML column and its index
The describe and describe_long operations display information about the XML columns and their indexes. If the table and column name are specified with the -l option, the specified XML column is described. If, instead, you specify a table owner with the -O option, this operation describes all XML columns in all tables owned by that owner and accessible by the connecting user. If neither -l nor -O options are specified, all XML columns accessible by the connecting user are described.
The describe operation displays:
- Table owner, table name, and XML column name
- Database name (if applicable)
- Privileges that user has on the XML column
- The configuration keyword (DBTUNE) used when the XML column was created
- The minimum XML ID for the column
- Whether the column has an associated XML index and, if so, the name and type of the index (index definition or index template)
sdexml -o describe -l sde.2005,xml -i 5151 -s circus -u sde
-p sdepass
XML
Administration Utility
-------------------------------------------------
Database
: SDE
Table Owner
: SDE
Table Name
: 2005
XML Column
: XML
User Privileges
: SELECT, UPDATE, INSERT, DELETE
XML Configuration : DEFAULTS
XML column is indexed.
index name : SDE.ims_xml134
index type : Index Definition
Storage type
: DB_XML
The describe_long operation displays all the information that the describe operation displays. It also displays:
- Index description (text)
- For every indexed tag:
- Tag name (path)
- Data type
- Description (if any)
- Alias (if any)
- A note if the tag is excluded
List the content of an XML document
The list operation displays the formatted contents of the XML document that is in the specified column and row. Specify the row using the value in the registered row ID column for the table. The sdexml command displays a message—XML document for row nnn:—followed by a formatted list of the XML document. If the content of the column in the specified row is NULL, this message—XML document is NULL—is displayed.
sdexml -o list -l sde.demo,xml -v 403 -i 5151 -s circus -u sde -p sdepass
Optimize the text index
The optimize operation optimizes the tag indexes on the documents in the XML column. You can continue querying from XML columns while optimization is running. This operation will help maintain the best possible search performance for users of Oracle databases by solving fragmentation issues due to frequent inserts/updates to the XML column. Users with other databases need not use this command. Using this command with other database types will return this message: XML column text index optimization not supported on this RDBMS.
Run the optimize operation after loading XML documents. You should also optimize the tag indexes when the count of new or changed XML documents in the database is more than 20 percent of the total number of XML documents.
sdexml -o optimize -l counties,xml -i esri_sde -s egan -u sde -p sigh
List XML column statistics
The stats operation displays statistics about all documents in the specified XML column. The document lengths are expressed in bytes.
sdexml -o stats -l info,ixmlcol -i 28991 -u myuser -p mypw
Number of XML documents found
: 323
Number of NULL documents found : 1
Total number of rows : 324
Average length of documents : 2309.25
Minimum document length : 190
Maximum document length : 45678
List XML column index statistics
The xi_stats operation displays statistics about the indexes on all documents in the specified XML column. For each indexed tag, sdexml displays the total number of times the tag appears in all documents, the number of documents it appears in, and the number of unique values contained by the tag. A summary is also displayed. If we ran the example above with the xi_stats operation instead of the stats operation, we would get the same results plus this summary:
tag count doc count unique
values tag name
--------- --------- ------------- --------
2 2 2 /metadata/county/name
Number of unique tag names : 1
Total number of tags indexed : 2
Number of XML documents indexed : 2
| Home Copyright © Environmental Systems Research Institute, Inc. 2004 - 2010. |