Say I have the following SQL query to work with

{for $each in /This/Company/Department[1]/Role/Person
return if ($each/Shoes/F[@C="9"]="657493742")
then (<ID>{$each/Shoes/F[@C="9916"]/V}</ID>)
else ()}
FROM mytable

Is there a way to craft a Selective XML index that would help in this situation? According to the documentation it sounds like it could help with filtering the nodes, but I haven't come across any examples of dealing with a query like this.

  • Please tag your SQL Server version.
    – McNets
    Commented Feb 20, 2018 at 20:20

1 Answer 1


From Selective XML Indexes (SXI), Supported Features, Prerequisites, and Limitations

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

query() is not supported so it will not work for you.

On the other hand it says that

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

I have not been able to produce a query that use XML indexes using the query() method so I can't really tell you in what cases it may work.

If you want to tackle a performance issue with your query I would recommend you to shred the XML using nodes() and value() and then reconstructing the XML using for xml path. It is usually faster than building the XML with query() and you could also probably make use of selective XML indexes if you need it.

  • Unfortunately I cannot change the query.
    – tre
    Commented Mar 9, 2018 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.