I spent the afternoon with Tony, the MVP behind sqlserverfaq and the UK SQL Server user group. Aside from getting to see him, his wife and their new son Tony had the need to consume a particular RSS feed onto his site, so I had to work for my dinner as well. The advantage of pulling RSS into a .net DataSet is that you can then leverage data binding to display the feed how you like. So, quick and dirty code time; basically we create an XmlTextReader pointing to the RSS feed and apply an XSLT to extract the information we want.

Why bother with an XSLT file? Well we want to use the DataSet.ReadXml method to fill a dataset, however ReadXml ignores namespaces. Why is this important? Some feeds use the slashcode namespace to provide a more detailed comment information. The RSS namespace already has a <comment> element and the slashcode namespace introduces an element with the same name (to see how this works in practice take a look at the Phil's RSS feed; you will see two comments elements, <comments> and <slash:comments>) You can have two elements with the same name as long as a namespace is specified on at least one of them. The problem arises with ReadXml; it strips namespaces from the element name when creating columns so an attempt to consume a feed such as Phil's will vomit exceptions as ReadXml tries to create two columns with the same name. So we apply a simple XLST to extract the bare minimum information we need. The XLST takes the title, link, description and guid from the RSS feed. You can of course adjust the fields you want by adding or deleting their element names between the <item> tag, as long as they are unique element names.

Now we need to grab the RSS feed, apply the XLST and then push it into a DataTable;

XmlTextReader rssReader = new XmlTextReader("rssFeedUrl");
XslTransform rssTransform = new XslTransform();
rssTransform.Load(Request.PhysicalApplicationPath+"rssToDataSet.xslt");
StringWriter rssWriter = new StringWriter();

XPathDocument rssXPath = new System.Xml.XPath.XPathDocument(rssReader);
rssTransform.Transform(rssXPath, null, rssWriter, null);
rssReader.Close();
rssWriter.Close();

System.IO.StringReader dataSetReader = new System.IO.StringReader(rssWriter.ToString());
DataSet dsRss = new DataSet();
dsRss.ReadXml(dataSetReader);
dataSetReader.Close();

And there it is, a quick RSS pull into a DataSet. Of course you need to add your own error checking and don't forget to Server.HtmlEncode anything you right out in order to avoid cross site scripting attacks through RSS.

Tony has put this to use by adding MSN searches to his site search, in order to do this you need to load an rssFeedUrl URL of http://search.msn.com/results.aspx?q=<yourQueryString>&format=rss. You will also need to URL encode your query string;

string msnSearchUri = String.Format(
  System.Globalization.CultureInfo.InvariantCulture,
  "http://search.msn.com/results.aspx?q={0}&format=rss",
  Server.UrlEncode(yourQueryTerms));

The current conditions state that XML results may not be used, reproduced or transmitted in any manner or for any purpose other than rendering MSN Search results within an RSS aggregator for your personal, non-commercial use. Please remember to ask permission if you're using them in any other way.