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.
Not possible in 2008 and earlier (i.e. as of when this question was originally posed) but in 2012 +, selective xml indexes were introduced, allowing you to do just that - index a specific (set) of path(s).
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With