We have the need to return a data field from a rowset XML column in SQL. The routine works but does not use any of the created XML indexes (Primary + Secondary ones on Value/Path/Property but no Selective XML indexes). Due to the amount of XML the constant shredding by the Query Engine is slowing down queries considerably. Anybody any ideas on how to get SQL Server (2012) to actually use the XML indexes on the XML column?
There are no speed differences, data read or plan diffs with XML indexes off or on so it is definitely not using them.
Example query (@Field is the field name required, @XmlData the XML).
@XmlData.value(
'declare namespace rs="urn:schemas-microsoft-com:rowset";
declare namespace z="#RowsetSchema";
(/xml/rs:data/rs:insert/z:row[@fm_field=sql:variable("@Field")]/@fm_data)[1]','nvarchar(255)')
Example XML snippet...
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly" rs:updatable="true">
<s:AttributeType name="fm_field" rs:number="1" rs:write="true">
<s:datatype dt:type="string" dt:maxLength="255" rs:precision="0" rs:fixedlength="true" rs:maybenull="false" />
</s:AttributeType>
<s:AttributeType name="fm_data" rs:number="2" rs:write="true">
<s:datatype dt:type="string" dt:maxLength="255" rs:precision="0" rs:fixedlength="true" rs:maybenull="false" />
</s:AttributeType>
<s:extends type="rs:rowbase" />
</s:ElementType>
</s:Schema>
<rs:data>
<rs:insert>
<z:row fm_field="ABSOLVELIA" fm_data="No" />
<z:row fm_field="ADJNO" fm_data="" />
<z:row fm_field="AIRPORTS" fm_data="No" />
There is a potentially large(ist) amount of XML data (about 15K average per record wth recordsets in the millions) in potentially some dbs of TB size each (most are smaller at only a few hundred GB so they are more managable).
It is taking all of single core CPU (XML is processed single core in the SQL engine due to the lower level processes SQL uses for parsing it) so any parsing of the XML information is taking forever with no avenue to throw more cores at it (we are using a modern 3GHz+ VM server farm for this bit).
To speed up reporting I added an indexed view for the client on over 400 XML field attributes they wish to report off for data mining, this index is built from a backup (they do not wish the live database to change or have anything that slows inserts or have anything replicating off it or indeed HA etc. on live so the Indexed view is built from a backup).
The XML format is not changable so the data being held in attributes (rowset format field/data) is stopping SQL from using any type of XML index (I know XML indexes are cr@p from past experience so I was clutching at straws).
I was hoping there was at least some way to get it to use the Primary index so the shred overhead was less but to no avail. Selective indexes fail also. From tracing this myself it has become apparent that with XML in this format we are stuck with a single core build for Indexed Views on the XML data.
I can code a multi-threaded pre-shredder in C# to get the speed and the core use but was hoping SQL had a workable answer I had missed.