Sql-server – Selective Xml Indexing

sql serversql-server-2012

I have recently been researching Selective Xml Indexing and have been trying to figure out how to index a .query method

If I have the below query

WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT 

    ,a.value('@Number' , 'INT') As Number
    ,c.query('.') as FormattedXml

    From

    dbo.XmlTable As X

    Cross Apply x.XmlColumn.nodes('Root') aa(a) 
    Cross Apply a.nodes('Block') bb(b)

I can write a selective Index as below if I am following it right:

CREATE SELECTIVE XML INDEX xsi_XmlTable
ON dbo.XmlTable( XmlColumn )
WITH XMLNAMESPACES ( Default 'http://www.w3.org/2001/XMLSchema' )
FOR
(
      root = '/Root' AS XQUERY 'node()'
    , rootNumber = '/Root/@Number' AS SQL INT SINGLETON

)
GO

Can I include the c.query in the index? Thanks

Best Answer

A selective index will not be used when using query to retrieve the data.

From Selective XML Indexes (SXI) - Supported XML Features

Selective XML indexes support the XQuery supported by SQL Server inside the exist(), value() and nodes() methods.

For the query() and modify() methods, selective XML indexes may be used for node filtering only.

For the query() method, selective XML indexes are not used to retrieve results.

However testing shows that the index is used when you rewrite the query using nodes. These queries returns the same but the second is faster because it uses the the selective XML index.

select data.query('/Log/CallStack/ExceptionThread/Line')
from LogData


select T.X.query('.')
from LogData
  cross apply data.nodes('/Log/CallStack/ExceptionThread/Line') as T(X)

The selective XML index used in the second query is defined as..

[Line] = '/Log/CallStack/ExceptionThread/Line' as XQUERY 'node()',

I used a table of my own just to be able to show what the difference in this case is.

(67992 row(s) affected)
Table 'LogData'. Scan count 1, logical reads 388, physical reads 0, read-ahead reads 0, lob logical reads 1840865, lob physical reads 313514, lob read-ahead reads 24.

 SQL Server Execution Times:
   CPU time = 129792 ms,  elapsed time = 180087 ms.

(1850106 row(s) affected)
Table 'xml_sxi_table_622625261_256000'. Scan count 1, logical reads 8416, physical reads 3, read-ahead reads 8403, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LogData'. Scan count 1, logical reads 388, physical reads 0, read-ahead reads 0, lob logical reads 927882, lob physical reads 0, lob read-ahead reads 24.

 SQL Server Execution Times:
   CPU time = 65271 ms,  elapsed time = 67636 ms.

Related Solutions

Sql-server – Sql Server 2012 Extended Events for Selective Xml Indexing not showing results

Not sure what you are seeing and not seeing in the event log but it works as expected for me (version 11.0.3000).

The mistake you did with your extra path expressions is that you forgot Stuff in the path.

Testing this with the extended events and looking at the execution plan for operator Table Valued Function XML Reader ... (there should be none), I found that one extra path is enough for your queries to make it use only the index to fetch the data [4] = '/Root/Stuff/Forms' as SQL nvarchar(25). You can have [3] = '/Root/Stuff/Forms' as XQUERY 'node()' in there as well and perhaps it will make a difference or not depending on what your XML look like.

Note 1: You have mixed up the names of the columns in the index and your query. That might contribute to the problems you have with extended event.

Note 2: The events for missing selective XML indexes is generated when the query is compiled. If the query plan is used from the cache you will not see any events.

Sql-server – Replace value of strongly typed xml element in SQL Server with XQuery

I have not found a simple way to just modify the replace value of statement to work with anonymous simple type definitions.

Simple repro of what you have:

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as 
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid">
                    <xs:simpleType>
                        <xs:restriction base="xs:string">
                            <xs:maxLength value="30"/>
                        </xs:restriction>
                    </xs:simpleType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';

set @X.modify('replace value of /root/xid  with "2"');

Result:

Msg 2247, Level 16, State 1, Line 25 XQuery [modify()]: The value is of type "xs:string", which is not a subtype of the expected type "<anonymous>".

One workaround is to modify your schema to use a named simple type xidType and cast the new value.

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as 
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid" type="xidType"/>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
    <xs:simpleType name="xidType">
        <xs:restriction base="xs:string">
            <xs:maxLength value="30"/>
        </xs:restriction>
    </xs:simpleType>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';

set @X.modify('replace value of /root/xid  with "2" cast as xidType?');

Another way is to extract the XML to an untyped XML variable, modify the variable and put it back to the table.

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as 
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid">
                    <xs:simpleType>
                        <xs:restriction base="xs:string">
                            <xs:maxLength value="30"/>
                        </xs:restriction>
                    </xs:simpleType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';
declare @X2 xml = @X;

set @X2.modify('replace value of (/root/xid/text())[1]  with "2"');
set @X = @X2;

Best Answer

Related Solutions

Sql-server – Sql Server 2012 Extended Events for Selective Xml Indexing not showing results

Sql-server – Replace value of strongly typed xml element in SQL Server with XQuery

Related Question