SQL Server XQuery For Idiots

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.

6 thoughts on “SQL Server XQuery For Idiots

  • 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

  • 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?)

  • scarydba

    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.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.