Using Yahoo Query Language (YQL) to count XML elements

A quick little YQL tip. I recently had the need to count the number of items in an RSS feed, but without downloading the feed itself as the application is bandwidth sensitive.  So I figured I’d use the Yahoo Query Language, which lets you define SQL queries for accessing all kinds of public data on the web, including of course RSS and Atom feeds.

However it’s implementation of the SQL count() function seems to be buggy, as you can’t simply do

select count(*) from xml where url=”http://path/To/Rss.xml” and and itemPath=”/rss/channel/item”

as it throws and error.  Putting “item” in the count() doesn’t work either.  However, if you try to select the text inside an element, it just returns a single text value, but you still get the count of rows returned, e.g.

select * from xml where url=”http://path/To/Rss.xml” and itemPath=”/rss/channel/item/title/text()”

returns JSON that looks like

 "query": {
  "count": "233",
  "created": "2010-04-19T10:50:42Z",
  "lang": "en-US",
  "results": "5 stars by Anonymous on February 26, 2010"

meaning you save a huge amount of bandwidth but still can get the count of the items in the RSS stream. Check out an example HERE, just press the Test button. Cool huh?