Is it possible to set an index inside a XML column on SQL Server 2005 / SQL Server 2008?

Question

Hi,

I have an application that stores xml documents inside a column on SQL Server. The structure of the XML document is similar to the one below:

<document>
    <item>
        ...
        <phoneNumber>0123456789</phoneNumber>
        ....
    </item>
    <item>
        ...
        <phoneNumber>9876543210</phoneNumber>
        ....
    </item>
    ...
</document>

Basically this column stores a set of customer information. The XML documents can have different child elements inside the <item> element, nevertheless some of these child elements are contained in all documents (e.g. the <phoneNumber> element in the above example).

This way I can have for example, one row in the table containing the following value

<document>
    <item>
        <firstName>Carlos</firstName>
        <lastName>Loth</lastName>
        <phoneNumber>0123456789</phoneNumber>
    </item>
    <item>
        <firstName>Alberto</firstName>
        <lastName>Tomatis</lastName>
        <phoneNumber>987654321</phoneNumber>
    </item>
</document>

And another row containing this document

<document>
    <item>
        <orderNumber>XYZ</orderNumber>
        <phoneNumber>0123456789</phoneNumber>
    </item>
    <item>
        <orderNumber>ABC</orderNumber>
        <phoneNumber>987654321</phoneNumber>
    </item>
</document>

So, my question is that is it possible to create an index on that XML column based on the document/item/phoneNumber element? I need to perform a query that returns the information stored in other "fixed known" columns based on the phoneNumber information.

Any suggestions or ideas?

Thanks in advance, Carlos Loth.

Answer 1

Yes.
SQL Server 2005 supports four different types of XML indexes. Since an XML index is somewhat different than a relational index, it is necessary to know their implementation before we approach how to use them for maximum effectiveness. There is a single "primary XML index" and three different flavors of "secondary XML index".

For more info, see this MSDN article

You need to create a primary XML index before you can define secondary XML indexes:

CREATE PRIMARY XML INDEX xml_idx ON your_table(xml_column)
CREATE XML INDEX xml_idx ON your_table(xml_column) FOR PROPERTY

Create XML Index documentation