I’m still struggling with learning XQuery. My latest little revelation was small, but vital.
The difference between:
@inXML.nodes('/rss/channel/item')
and
@inXML.nodes('/rss[1]/channel[1]/item')
Is the difference between a 10 minute query and a 4 second query. Kind of shocking really. My understanding is that XQuery assumes there are multiple possible paths that look like “/rss/channel” so it searches over and over again through the XML to see if it can find them. But by identifying it as a root, showing that only one possible path is available, it stops stumbling around in the dark and simply reads the data.
It’s a little thing, but it made an enormous difference. I’ve still got a long way to go in learning how to use XPath within XQuery.
UPDATED: I modified the title so that it’s clear I’m talking about SQL Server here.
Even worse is doing //rss/channel.
At least /rss/channel will find rss nodes just under the root and then will look for all the channel nodes under those.
But //rss/channel will find ALL rss nodes ANYWHERE in the document and then will look for all the channel nodes under those.
And then there’s //rss//channel. You can guess what that does. Yeesh!
I’ve seen a lot of “solutions” posted on the MSDN T-SQL forum that use the // construct… not a good idea with a large XML document.
–Brad
Oh, good catch. I knew about that problem as well. I should have put it in there. Thanks for outlining it so well.
A useful tip – but only for people who know which XQuery product you are using. A different XQuery implementation with a different optimizer might give completely different results, and the best way to write a query on one system is not necessarily the best way to write it on another.
Frankly, this result suggests that your XQuery implementation is not particularly smart, so it would be interesting to know which it is. (From your tagline, might one assume SQL Server?)
You know, I hadn’t thought of that, but you’re right. I didn’t mention it was for SQL Server at all.
And don’t judge SQL Server’s implementation of XQuery too harshly by what I’m writing. I’m more than a little bit dense on the topic, hence the tips for idiots, like myself.
Oh, and I’ll modify the title, just for the next person that stumbles across it.
Very useful tip and simple explanation! I have put this in practice.
Thank you!