Generating RSS/ATOM feeds with TSQL

RSS/ATOM feeds are 'must-to-have' ingredient of any web site today. Most of the times, feeds are generated from the data stored in a database. In a typical architecture, an application would fetch the data from the database and would generate the feed at the application layer, either using a 3rd party library like RSS.NET or ATOM.NET or by using custom programming using .NET/other XML libraries.

Given the XML capabilities of SQL Server, some times, it would make more sense to generate the feed at the database side and return the XML feed to the application. Depending upon the specific requirement that you have, this may or may not be suitable to your specific application. I would like to know how many of you have found this approach suitable for your specific application requirements.

I had been writing a series of articles on generating RSS/ATOM feeds with TSQL at www.sqlservercentral.com. Some of them are published and some are still waiting. Below is a list of articles already published. I will update this list once the other articles are published.

1. XML Workshop XVIII - Generating an RSS 2.0 Feed with TSQL - If you are already on SQL Server 2005, you could benefit from this this approach. This article shows the code that generates an RSS 2.0 feed using FOR XML with PATH. PATH is introduced with SQL Server 2005 and hence if you are working with SQL Server 2000, you cannot use this approach.
2. XML Workshop XIX - Generating an ATOM 1.0 Feed with TSQL - This article shows the code that generates an ATOM 1.0 feed using TSQL keyword FOR XML with PATH. Again, this is specific to SQL Server 2005/2008. If you are working with SQL Server 2000, then you cannot use the approach explained in this article.
3. XML Workshop XX - Generating an RSS 2.0 Feed with TSQL(SQL server 2000) - Well, this is for SQL Server 2000. This article shows the code needed to generate an RSS 2.0 feed using FOR XML with EXPLICIT. EXPLICIT is available in SQL Server 2000/2005/2008 and hence this approach will suite you, if you are working with SQL Server 2000. The code using EXPLICIT might look really complex. So, this code may be ideal only if you are working with SQL Server 2000. If you are on 2005/2008, the approach using PATH will be recommended.
4. XML Workshop XX - Generating an ATOM 1.0 feed with TSQL using FOR XML EXPLICIT - This is the ATOM 1.0 version of the above code. This is a continuation of the previous article which explained how to generate an ATOM 1.0 feed with FOR XML PATH. FOR XML PATH is only available from SQL Server 2005 and above. This article demonstrates how to generate a valid ATOM 1.0 feed using FOR XML EXPLICIT which is ideal for SQL Server 2000.
5. XML Workshop XV - Accessing FOR XML results with ADO.NET - You might need this if you are generating your RSS/ATOM feeds in TSQL. This explains how to pass the XML results to your .NET application. This article has VB.NET and C#.NET sample code (ADO.NET) that explain how call a stored procedure that returns an XML stream and access the XML document returned by the stored procedure.
6. XML Workshop XXII - A TSQL RSS Library - This article presents a function that generates an RSS 2.0 feed. Select the columns/values you want to include in your feed and pass them to the function as an XML variable. The function will create a correct RSS2.0 feed from the information being passed into it.