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 Feb 20 '18 at 20:20

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.

| improve this answer | |
  • Unfortunately I cannot change the query. – tre Mar 9 '18 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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