In today’s web development landscape, RSS/ATOM feeds have become an integral part of most websites. These feeds allow users to easily subscribe to updates and receive the latest content from their favorite websites. In this article, we will explore how to generate an RSS 2.0 feed using TSQL in SQL Server.

RSS 2.0 is the most widely used version of the RSS specification. It has certain mandatory elements and attributes that need to be included in the feed. Additionally, values like the publication date should be in a specific format. Most RSS readers validate the feed against these rules and reject it if it doesn’t comply with the specification.

To generate an RSS feed in SQL Server, we can leverage the XML capabilities of the database. We will create two tables: one to store information about the RSS channel and another to store the data for each RSS item. We will then use the FOR XML PATH clause to shape the data into the desired XML structure.

Let’s start by creating the tables:

CREATE TABLE channel(
    Title VARCHAR(100),
    Link VARCHAR(100),
    Description VARCHAR(200),
    WebMaster VARCHAR(50),
    Language VARCHAR(20),
    ImageUrl VARCHAR(100),
    ImageTitle VARCHAR(100),
    ImageLink VARCHAR(100),
    ImageWidth SMALLINT,
    ImageHeight SMALLINT,
    CopyRight VARCHAR(100),
    LastBuildDate DATETIME,
    ttl SMALLINT
);

CREATE TABLE Articles(
    Title VARCHAR(100),
    Link VARCHAR(100),
    Description VARCHAR(200),
    Guid VARCHAR(100),
    PubDate DATETIME
);

Next, let’s populate the tables with some sample data:

INSERT INTO channel (
    Title, Link, Description, Webmaster, Language, ImageUrl, ImageTitle, ImageLink, ImageWidth, ImageHeight, CopyRight, LastBuildDate, ttl
)
SELECT 'Welcome to XML Workshop', 'http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html', 'A collection of short articles on SQL Server and XML', 'jacob@dotnetquest.com (Jacob Sebastian)', 'en-us', 'http://www.sqlserverandxml.com/image.jpg', 'Welcome to XML Workshop', 'http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html', 144, 22, 'Jacob Sebastian. All rights reserved.', '2008-03-12 23:45:02', 100;

INSERT INTO Articles (
    Title, Link, Description, Guid, PubDate
)
SELECT 'XML Workshop I - Generating XML with FOR XML', 'http://www.sqlservercentral.com/columnists/jSebastian/2982.asp', 'A short article that explains how to generate XML output with TSQL keyword FOR XML', 'http://www.sqlservercentral.com/columnists/jSebastian/2982.asp', '2008-03-12 23:45:02'
UNION ALL
SELECT 'XML Workshop II - Reading values from XML variables', 'http://www.sqlservercentral.com/articles/Miscellaneous/2996/', 'This article explains how to read values from an XML variable using XQuery', 'http://www.sqlservercentral.com/articles/Miscellaneous/2996/', '2008-03-12 23:45:02';

Now, let’s generate the item elements for the RSS feed:

SELECT
    Title AS title,
    Link AS link,
    Description AS description,
    'true' AS 'guid/@isPermaLink',
    Guid AS guid,
    LEFT(DATENAME(dw, PubDate),3) + ', ' + STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT') AS pubDate
FROM
    Articles
FOR XML PATH('item'), TYPE

Next, let’s generate the channel element:

SELECT
    Title AS title,
    Link AS link,
    Description AS description,
    Webmaster AS webMaster,
    Language AS language,
    ImageUrl AS 'image/url',
    ImageTitle AS 'image/title',
    ImageLink AS 'image/link',
    ImageWidth AS 'image/width',
    ImageHeight AS 'image/height',
    CopyRight AS copyright,
    LEFT(DATENAME(dw, LastBuildDate),3) + ', ' + STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT') AS lastBuildDate,
    Ttl AS ttl,
    (
        SELECT
            Title AS title,
            Link AS link,
            Description AS description,
            'true' AS 'guid/@isPermaLink',
            Guid AS guid,
            LEFT(DATENAME(dw, PubDate),3) + ', ' + STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT') AS pubDate
        FROM
            Articles
        FOR XML PATH('item'), TYPE
    )
FROM
    channel
FOR XML PATH('channel'), TYPE

Finally, let’s add the root element and generate the XML header:

SELECT
    '' +
    (
        SELECT
            '2.0' AS '@version',
            (
                SELECT
                    Title AS title,
                    Link AS link,
                    Description AS description,
                    Webmaster AS webMaster,
                    Language AS language,
                    ImageUrl AS 'image/url',
                    ImageTitle AS 'image/title',
                    ImageLink AS 'image/link',
                    ImageWidth AS 'image/width',
                    ImageHeight AS 'image/height',
                    CopyRight AS copyright,
                    LEFT(DATENAME(dw, LastBuildDate),3) + ', ' + STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT') AS lastBuildDate,
                    Ttl AS ttl,
                    (
                        SELECT
                            Title AS title,
                            Link AS link,
                            Description AS description,
                            'true' AS 'guid/@isPermaLink',
                            Guid AS guid,
                            LEFT(DATENAME(dw, PubDate),3) + ', ' + STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT') AS pubDate
                        FROM
                            Articles
                        FOR XML PATH('item'), TYPE
                    )
                FROM
                    channel
                FOR XML PATH('channel'), TYPE
            )
        FOR XML PATH('rss')
    )

By executing the above code, we will generate the desired RSS 2.0 feed in XML format. You can then validate the generated feed using an online feed validator like FeedValidator.org to ensure it complies with the RSS specification.

In conclusion, SQL Server’s XML capabilities allow us to generate an RSS 2.0 feed using TSQL. By shaping the data with the FOR XML clause, we can easily create a valid RSS feed that adheres to the required structure and rules. This provides a convenient way to generate feeds directly from the database without the need for custom application code or third-party libraries.