OK, so let's try something. Load a bunch of documents. Although, actually this part works with an empty table.
create selective xml index fooidx2 on foo(thexml)
for
(
pathname1 = 'foo/bar/baz2'
);
Msg 6379, Level 16, State 201, Line 1
selective XML index 'fooidx' already exists on column 'thexml' in table 'foo'.
Multiple selective XML indexes per column are not allowed.
Only one SXI at a time. With 1-n path specifications.
About path specifications. To paraphrase the talk…there are three basic types of paths.
1. Simple: /foo/bar/baz1
2. With optimizations:
/foo/bar/baz2 as XQUERY 'xs:string' MAXLENGTH(25)
/foo/bar/baz3 as XQUERY 'xs:double' SINGLETON
/foo/bar/baz4 as XQUERY node() — check for node existance
3. For use with the XQuery value method:
/foo/bar/baz5 as SQL VARCHAR(25)
Forms 1 and 2 are for XQuery with any XQuery method. Form 3 is for XQuery value method. This seems to turn out to be more important than I thought it was at first.
Rule for using it in query plan:
1. If SXI (selective XML index) exists and path matches specification, use it
2. If PXI (primary XML index) exists, use it
3. Else use XML Reader with XPath Filter
First experiment:
Defined (the one and only one) SXI index for this XML column on path /foo/bar/baz. Type 1 path specification. Issued query:
SELECT thexml.value('(/foo/bar/baz)[1]', 'varchar(25)')
FROM foo;
SXI isn't used in the plan. Hmm…he said it was a decision of the algebrizer, not the optimizer. And path sure looks like it matches. Futz with this for an hour or so. I actually added more documents. Used three path specs that (I thought) all matched. I thought this worked in his demo, but no joy. Tried to force the SXI with index hint (you can't force an SXI in any case). But it's the algebrizer, not the optimizer, Bob…
Instead try index on /foo/bar/baz AS SQL VARCHAR(25). Type 3 path specification. Now, it's used.
@bobbeauch