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 element, nevertheless some of these child elements are contained in all documents (e.g. the 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.