Primary XML Index |
This indexes all tags, values, and paths within the XML instances in an XML column. The base table (that is, the table in which the XML column occurs) must have a clustered index on the primary key of the table. The primary key is used to correlate index rows with the rows in the base table. The following statement creates a primary XML index called idx_xml_data on the XML column xml_data of the table Employee: |
CREATE PRIMARY XML INDEX idx_xml_data on Employee (xml_data) |
Secondary XML Indexes |
After the primary XML index has been created, you may want to create secondary XML indexes to speed up different classes of queries within your workload. There are three types of secondary XML indexes named PATH, PROPERTY, and VALUE that can benefit path-based queries, custom property management scenarios, and value-based queries, respectively. |
The PATH index builds a B+-tree on the (path, value) pair of each XML node in document order over all XML instances in the column. The PROPERTY index creates a B+-tree clustered on the (PK, path, value) pair within each XML instance, where PK is the primary key of the base table. Finally, the VALUE |
index creates a B+-tree on the (value, path) pair of each node in document order across all XML instances in the XML column. |
If your workload uses path expressions heavily on XML columns, the PATH sec- ondary XML index is likely to speed up your workload. If your workload retrieves multiple values from individual XML instances using path expressions, clustering paths within each XML instance in the PROPERTY index may be helpful. If your workload involves querying for values within XML instances without knowing the element or attribute names that contain those values, you may want to create the |
VALUE index. |
To create a PATH index on the xml_data column, use the following command. |
CREATE XML INDEX idx_xml_data_path on Employee (xml_data) |
USING XML INDEX idx_xml_data FOR PATH |
learn guitarphysics learnteliphonyxmlphysicsenjoylife