SQL Server XQuery For Idiots

November 30, 2009 at 9:17 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , )

I’m still struggling with learning XQuery. My latest little revelation was small, but vital.

The difference between:




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.


  1. Brad Schulz said,

    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.


  2. scarydba said,

    Oh, good catch. I knew about that problem as well. I should have put it in there. Thanks for outlining it so well.

  3. Michael Kay said,

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

  4. scarydba said,

    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.

  5. scarydba said,

    Oh, and I’ll modify the title, just for the next person that stumbles across it.

  6. Raghuram (AJ) said,

    Very useful tip and simple explanation! I have put this in practice.

    Thank you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

%d bloggers like this: