\nI've only been talking so far about the "primary" Selective XML Index. But you can also create 0-n "secondary" Selective XML Indexes. The syntax looks a little bit like secondary "non-selective" XML Indexes in that you use the "USING [related XML index]". You specify one (and only one) pathspec. Additionally, the XML value that the pathspec "points to" (see previous post) cannot be data type xs:untypedAtomic (varbinary(max)).\n<\/p>\n
\ncreate xml index sxi_secondary1 on foo(thexml)
\nusing xml index fooidx
\nfor (pathname1); — strongly typed to SQLVARCHAR(25)\n<\/p>\n
\ncreate xml index sxi_secondary2 on foo(thexml)
\nusing xml index fooidx
\nfor (anotherxq); — strongly typed to XQUERY xs:double (SQL FLOAT)\n<\/p>\n
\n— Msg 102, Level 15, State 1, Line 3
\n— Incorrect syntax near ','.
\n— Means: error: only one pathspec name allowed
\ncreate xml index sxi_secondary3 on foo(thexml)
\nusing xml index fooidx
\nfor (pathname1, pathname2);\n<\/p>\n
\n— Msg 6391, Level 16, State 0, Line 1
\n— Path 'pathname2' is promoted to a type that is invalid for use as a key column in a secondary selective XML index.\n<\/p>\n
\n— Means: can't have varbinary(max) as index key
\ncreate xml index sxi_secondary3 on foo(thexml)
\nusing xml index fooidx
\nfor (pathname2);\n<\/p>\n
\nThese work the way you'd think. They create a NONCLUSTERED index over the (internal) table that comprises the "primary" Selective XML Index. The NONCLUSTERED index key is the "value" column in the path spec, along with the traditional "backpointer" to the primary key of the "primary" Selective XML index. These NONCLUSTERED index are FILTERED indexes, filtered on the value column in the path spec.The filter is "[Valuecolumn] IS NOT NULL"\n<\/p>\n
\nSo, if an XML value in a pathspec is sparse (meaning, it doesn't appear in every\/most rows) these secondary Selective XML Indexes could be helpful. And remember, like Michael said, these indexes are chosen by the optimizer, not the algebrizer, until the way the algebrizer chooses the primary Selective XML index, but after the algebrizer decides that the Selective XML Index could be useful at all.\n<\/p>\n
\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"
I've only been talking so far about the "primary" Selective XML Index. But you can also create 0-n "secondary" Selective XML Indexes. The syntax looks a little bit like secondary "non-selective" XML Indexes in that you use the "USING [related XML index]". You specify one (and only one) pathspec. Additionally, the XML value that the […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,37],"tags":[],"class_list":["post-469","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-sql-server-xml"],"yoast_head":"\n