XML schemas and XML indexes

If you validate your XML documents against an XML schema, ensure that the data type specifications in the XML schema match the data types that you use for your indexes.

Example: Suppose that an XML schema for documents in the Description column of the sample Product table looks like this:

<?xml version="1.0"?>
<xs:schema targetNamespace="http://posample.org" 
 xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
 elementFormDefault="qualified">
  <xs:element name="product">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="description" minOccurs="0" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="name">
               <xs:simpleType>
                <xs:restriction base="xs:string">
                 <xs:maxLength value="20"/>
                </xs:restriction>
               </xs:simpleType>
              <xs:element name="details" type="xs:string" minOccurs="0" />
              <xs:element name="price" type="xs:decimal" minOccurs="0" />
              <xs:element name="weight" type="xs:string" minOccurs="0" />

              <xs:element name="batteries" nillable="true" minOccurs="0" 
               maxOccurs="unbounded">
                <xs:complexType>
                  <xs:simpleContent>
                    <xs:extension base="xs:string">
                      <xs:attribute name="included" type="xs:string" />
                    </xs:extension>
                  </xs:simpleContent>
                </xs:complexType>
              </xs:element>

            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="pid" type="xs:string" />
    </xs:complexType>
  </xs:element>
</xs:schema>

After looking at the queries you need to execute, you determine that you need indexes on name and price. The XML schema provides guidance on what data type to pick for the index: the price element is decimal, so you need to choose the DECFLOAT data type for the index on price. name has a string data type, with a maximum length of 20 Unicode characters. You should choose the VARCHAR(80) type for the index on name. The maximum length of 80 bytes ensures that the index can accommodate the largest possible name value, which is a 20-character name in which each character has the maximum length of four bytes. Your indexes might look like this:

CREATE INDEX priceindex on Product(Description)
 GENERATE KEY USING XMLPATTERN '/product/description/price' AS DECFLOAT
CREATE INDEX colorindex on company(productdocs)
 GENERATE KEY USING XMLPATTERN '/product/description/name' AS SQL VARCHAR(80)