In part 1 of this series, we tried a simple example from the Books Online without any SXI index at all, and an SXI with all the paths covered. No XEvents were emitted in either case. Now let’s change the XSI around (by dropping and recreating, although we could use ALTER, so envision a drop in between each example) to see what triggers the XEvents and what the query plan effects are.<\/p>\n
— first let’s back off one path
\nCREATE SELECTIVE XML INDEX simple_sxi ON Tbl(xmlcol)
\nFOR (
\npath123 =\u00a0 ‘\/a\/b’ as XQUERY ‘node()’
\n— path124 =\u00a0 ‘\/a\/b\/c’
\n);<\/p>\n
— now, you get XML Reader, XML Reader with XPath Filer, CI Seek on SIDX, CI Scan on base table
\n— and “selective_xml_index_path_not_indexed” XEvent for \/a\/b\/c
\nSELECT T.id FROM tbl T WHERE T.xmlcol.exist(‘\/a\/b[c = “43”]’) = 1<\/p>\n
— how about adding the SINGLETON keyword and XQuery Type?
\nCREATE SELECTIVE XML INDEX simple_sxi ON Tbl(xmlcol)
\nFOR (
\npath123 =\u00a0 ‘\/a\/b’ as XQUERY ‘node()’,
\npath124 =\u00a0 ‘\/a\/b\/c’ as XQUERY ‘xs:string’ SINGLETON
\n);<\/p>\n
— now, you get a single CI Seek on SIDX and CI Scan on base table
\nSELECT T.id FROM tbl T WHERE T.xmlcol.exist(‘\/a\/b[c = “43”]’) = 1<\/p>\n
— how about an XML.value query instead of XML.exists?
\n— get XML Reader with XPath Filter, XML Reader, and CI Scan on base table
\n— and “selective_xml_index_no_compatible_sql_type” XEvent for \/a\/b\/c
\nSELECT T.id FROM tbl T WHERE T.xmlcol.value(‘(\/a\/b\/c)[1]’, ‘nvarchar(10)’) = ’43’;<\/p>\n
— For this to use SXI, we need:
\nALTER INDEX simple_sxi ON Tbl FOR (add path124s = ‘\/a\/b\/c’ as SQL nvarchar(10) SINGLETON);<\/p>\n
— SXI CI Seek and CI Scan on base table, no XEvents
\nSELECT T.id FROM tbl T WHERE T.xmlcol.value(‘(\/a\/b\/c)[1]’, ‘nvarchar(10)’) = ’43’;<\/p>\n
BTW, if you use ‘varchar(10)’ rather than ‘nvarchar(10)’ in the query, the SXI won’t be used. One last one, let’s see if the SXI will be used if we specify an incorrect XQuery type:<\/p>\n
CREATE SELECTIVE XML INDEX simple_sxi ON Tbl(xmlcol)
\nFOR (
\npath123 =\u00a0 ‘\/a\/b’ as XQUERY ‘node()’,
\npath124 =\u00a0 ‘\/a\/b\/c’ as XQUERY ‘xs:double’ SINGLETON
\n);<\/p>\n
— We get “selective_xml_index_no_compatible_xsd_types” XEvent for \/a\/b\/c
\n— And XML Reader with XPath Filter, XML Reader, CI Seek on SIDX and CI Scan on base table
\nSELECT T.id FROM tbl T WHERE T.xmlcol.exist(‘\/a\/b[c = “43”]’) = 1;<\/p>\n
So, to summarize, in this post and the last post we’ve shown:
\nThe SXI cannot be used to search for mixed content.
\nThe ‘AS XQUERY node()’ specification can be used to check for existance of non-terminating node.
\nThe XEvents for SXI can guide you to understanding which nodes to specify.
\nYou must use “As SQL…” hint in order for SXI to be used in XML.value method.
\nYou use “As XQUERY…” hint to use SXI for XQuery predicates or paths in the XML.exist method.<\/p>\n
To go back to the BOL examples and test your understanding, write a selective XML index create statement for the following:<\/p>\n
SELECT T.record,
\nT.xmldata.value(‘(\/a\/b\/c\/d\/e[.\/f = “SQL”]\/g)[1]’, ‘nvarchar(100)’)
\nFROM myXMLTable T<\/p>\n
Cheers, Bob<\/p>\n
@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"
In part 1 of this series, we tried a simple example from the Books Online without any SXI index at all, and an SXI with all the paths covered. No XEvents were emitted in either case. Now let’s change the XSI around (by dropping and recreating, although we could use ALTER, so envision a drop […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,31,37],"tags":[],"class_list":["post-1645","post","type-post","status-publish","format-standard","hentry","category-event-notifications","category-sql-server-2012","category-sql-server-xml"],"yoast_head":"\n