Selective XML Indexes – Learning the rules through error messages

Experimenting without docs is a good way to get lots of error messages along the way. I'm learning by attrition, and it seems like I'd be better off knowing the rules. It's not the best way to learn anything, but you can at least learn the limits through the error messages. I was tired of trying seemingly random things and being surprised, so I decided to look for "selective XML index" error messages.

So, here's how to find (or imply) "the rules" for Selective XML Indexes.

— Ids 662, 970, 2735, 6342, 6343 and 6367-6399 and 9535-9539 inclusive
select message_id, text from sys.messages
where language_id = 1033
and text like '%selective XML%';

Now some of these are general (parameterized) error messages, so you won't get exact limits in terms of numbers. But, I tried the catagorize and summarize these for my own use. Here's some interesting ones.

1. Not all SQL data types are supported (not surprising)
2. Not all XQUERY (XSD) data types are supported (there were some surprises here, like xs:integer not supported)
3. Selective XML indexes' typing can't contradict types in an XML SCHEMA COLLECTION
4. Selective XML indexes' typing can affect new data you try and add. For example, if the types don't match the SXI-defined types and you're not using an XML SCHEMA COLLECTION.
5. You can't index a non-leaf node for the value or use * wildcard as the last step in the path.
6. There is a maximum depth to the path in a pathspec.

And so on… you can read 'em as well as I can.

Hopefully, this, and the previous entries should be enough to get you started. I did write these in one sitting and most of them were written in "realtime" so if you have any additions, questions, and/or find something neat to do with these drop me a line by email (be creative, figure out my email address) or ping me on twitter.

Motivation in the next (which is also the last, for now) posting.


Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.