Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

XML Workshop XVIII - Generating an RSS 2.0 Feed with TSQL

By Jacob Sebastian,

Introduction

In the previous sessions of XML Workshop, we have seen several XML processing examples. Some of the examples demonstrated how to generate XML output in a specific structure. In this session of XML Workshop, let us see how to generate an RSS feed using TSQL. Most web sites today adhere to the Web 2.0 standards and RSS/ATOM feeds are an integral part of them. If you are not familiar with RSS feeds, you can find a basic introduction here. RSS has several versions and the one that is widely used today is version 2.0. You can find the documentation of RSS 2.0 here.

Most of the times, web applications generate feeds at the application layer. The feed generator would execute a query/stored-procedure, fetch the data and generate the XML document using custom application code or a third party library like RSS.NET. The purpose of this session is to look deeper into the XML capabilities of SQL Server 2005 and see if it could generate an RSS 2.0 with a FOR XML operation. The examples and code given in this article is purely for the purpose of explaining how XML data can be generated in TSQL.

As mentioned earlier, an RSS 2.0 feed should follow a certain structure and rules. There are certain mandatory elements and attributes. Certain values like pubDate should be in a specific format. Most applications that read RSS feeds (RSS Readers) validate the feed against the rules given in the RSS specification and will reject the feed if it does not follow the rules defined in the specification. An online feed validator like FeedValidator.org can be used to validate the RSS feed to make sure that it follows all the rules defined by the RSS specification. In this session, we will generate an RSS 2.0 feed and validate it with FeedValidator.org.

Sample Feed

For the purpose of this example, let us assume that we need to create an RSS 2.0 feed that contains all the articles in the XML Workshop series. To keep the examples simple, we will process only two records. Here is the output that we expect to generate by the end of this LAB.

<?xml version="1.0" encoding="utf-8" ?>

<rss version="2.0">

  <channel>

    <title>Welcome to XML Workshop</title>

    <link>http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html</link>

    <description>A collection of short articles on SQL Server and XML</description>

    <webMaster>jacob@dotnetquest.com (Jacob Sebastian)</webMaster>

    <language>en-us</language>

    <image>

      <url>http://www.sqlserverandxml.com/image.jpg</url>

      <title>Welcome to XML Workshop</title>

      <link>http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html</link>

      <width>144</width>

      <height>22</height>

    </image>

    <copyright>Jacob Sebastian. All rights reserved.</copyright>

    <lastBuildDate>Wed, 12 Mar 2008 23:45:02 GMT</lastBuildDate>

    <ttl>100</ttl>

    <item>

      <title>XML Workshop I - Generating XML with FOR XML</title>

      <link>http://www.sqlservercentral.com/columnists/jSebastian/2982.asp</link>

      <description>A short article that explains how to generate XML output with TSQL keyword FOR XML</description>

      <guid isPermaLink="true">http://www.sqlservercentral.com/columnists/jSebastian/2982.asp</guid>

      <pubDate>Wed, 12 Mar 2008 23:45:02 GMT</pubDate>

    </item>

    <item>

      <title>XML Workshop II - Reading values from XML variables</title>

      <link>http://www.sqlservercentral.com/articles/Miscellaneous/2996/</link>

      <description>This article explains how to read values from an XML variable using XQuery</description>

      <guid isPermaLink="true">http://www.sqlservercentral.com/articles/Miscellaneous/2996/</guid>

      <pubDate>Wed, 12 Mar 2008 23:45:02 GMT</pubDate>

    </item>

  </channel>

</rss>

Before we proceed further, we need to make sure that the XML output that we intend to generate is a valid RSS 2.0 document. You could test this by using feedvalidator.org. Open a browser window and navigate to feedvalidator.org. Enter the url of the above sample RSS 2.0 feed and click on the "validate" button.

Sample Tables and Data

Let us create two tables to store the data needed for this LAB. We need one table to store the information about the RSS Channel and another table for storing the data of each RSS item. Here is the script for those 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 )

GO

 

CREATE TABLE Articles(

    Title VARCHAR(100),

    Link VARCHAR(100),

    Description VARCHAR(200),

    Guid VARCHAR(100),

    PubDate DATETIME )

GO

Here is the code to 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'

Generating the feed

Let us start with the item element, which is pretty much easy. Here is the code that generates the item elements.

SELECT

    Title AS title,

    Link AS link,

    Description AS description,

    'true' AS 'guid/@isPermaLink',

    Guid AS guid,

    PubDate    AS pubDate

FROM Articles FOR XML PATH('item'), TYPE

The above code produces the following XML document.

<item>

    <title>XML Workshop I - Generating XML with FOR XML</title>

    <link>http://www.sqlservercentral.com/columnists/jSebastian/2982.asp</link>

    <description>A short article that explains how to generate XML output with TSQL keyword FOR XML</description>

    <guid isPermaLink="true">http://www.sqlservercentral.com/columnists/jSebastian/2982.asp</guid>

    <pubDate>2008-03-12T23:45:02</pubDate>

</item>

<item>

    <title>XML Workshop II - Reading values from XML variables</title>

    <link>http://www.sqlservercentral.com/articles/Miscellaneous/2996/</link>

    <description>This article explains how to read values from an XML variable using XQuery</description>

    <guid isPermaLink="true">http://www.sqlservercentral.com/articles/Miscellaneous/2996/</guid>

    <pubDate>2008-03-12T23:45:02</pubDate>

</item>

Though the XML looks good, there is a problem. The format of the date value (pubDate) is not correct. RSS 2.0 requires that the date value should be in RFC 822 date format. So we need to format the date value to a valid RFC 822 date value. Here is the modified version of the code.

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

Here is the corrected XML.

<item>

    <title>XML Workshop I - Generating XML with FOR XML</title>

    <link>http://www.sqlservercentral.com/columnists/jSebastian/2982.asp</link>

    <description>A short article that explains how to generate XML output with TSQL keyword FOR XML</description>

    <guid isPermaLink="true">http://www.sqlservercentral.com/columnists/jSebastian/2982.asp</guid>

    <pubDate>Wed, 12 Mar 2008 23:45:02 GMT</pubDate>

</item>

<item>

    <title>XML Workshop II - Reading values from XML variables</title>

    <link>http://www.sqlservercentral.com/articles/Miscellaneous/2996/</link>

    <description>This article explains how to read values from an XML variable using XQuery</description>

    <guid isPermaLink="true">http://www.sqlservercentral.com/articles/Miscellaneous/2996/</guid>

    <pubDate>Wed, 12 Mar 2008 23:45:02 GMT</pubDate>

</item>

Now, let us write the query to 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


<channel>

    <title>Welcome to XML Workshop</title>

    <link>http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html</link>

    <description>A collection of short articles on SQL Server and XML</description>

    <webMaster>jacob@dotnetquest.com (Jacob Sebastian)</webMaster>

    <language>en-us</language>

    <image>

        <url>http://www.sqlserverandxml.com/image.jpg</url>

        <title>Welcome to XML Workshop</title>

        <link>http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html</link>

        <width>144</width>

        <height>22</height>

    </image>

    <copyright>Jacob Sebastian. All rights reserved.</copyright>

    <lastBuildDate>Wed, 12 Mar 2008 23:45:02 GMT</lastBuildDate>

    <ttl>100</ttl>

    <item>

        <title>XML Workshop I - Generating XML with FOR XML</title>

        <link>http://www.sqlservercentral.com/columnists/jSebastian/2982.asp</link>

        <description>A short article that explains how to generate XML output with TSQL keyword FOR XML</description>

        <guid isPermaLink="true">http://www.sqlservercentral.com/columnists/jSebastian/2982.asp</guid>

        <pubDate>Wed, 12 Mar 2008 23:45:02 GMT</pubDate>

    </item>

    <item>

        <title>XML Workshop II - Reading values from XML variables</title>

        <link>http://www.sqlservercentral.com/articles/Miscellaneous/2996/</link>

        <description>This article explains how to read values from an XML variable using XQuery</description>

        <guid isPermaLink="true">http://www.sqlservercentral.com/articles/Miscellaneous/2996/</guid>

        <pubDate>Wed, 12 Mar 2008 23:45:02 GMT</pubDate>

    </item>

</channel>

The next step is to add the root element and generate the xml header. here is the final version of the code.

SELECT

'<?xml version="1.0" encoding="utf-8" ?>' +

( 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') )


<?xml version="1.0" encoding="utf-8" ?>

<rss version="2.0">

    <channel>

        <title>Welcome to XML Workshop</title>

        <link>http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html</link>

        <description>A collection of short articles on SQL Server and XML</description>

        <webMaster>jacob@dotnetquest.com (Jacob Sebastian)</webMaster>

        <language>en-us</language>

        <image>

            <url>http://www.sqlserverandxml.com/image.jpg</url>

            <title>Welcome to XML Workshop</title>

            <link>http://www.sqlserverandxml.com/2007/12/xml-workshop-at-sql-server-central.html</link>

            <width>144</width>

            <height>22</height>

        </image>

        <copyright>Jacob Sebastian. All rights reserved.</copyright>

        <lastBuildDate>Wed, 12 Mar 2008 23:45:02 GMT</lastBuildDate>

        <ttl>100</ttl>

        <item>

            <title>XML Workshop I - Generating XML with FOR XML</title>

            <link>http://www.sqlservercentral.com/columnists/jSebastian/2982.asp</link>

            <description>A short article that explains how to generate XML output with TSQL keyword FOR XML</description>

            <guid isPermaLink="true">http://www.sqlservercentral.com/columnists/jSebastian/2982.asp</guid>

            <pubDate>Wed, 12 Mar 2008 23:45:02 GMT</pubDate>

        </item>

        <item>

            <title>XML Workshop II - Reading values from XML variables</title>

            <link>http://www.sqlservercentral.com/articles/Miscellaneous/2996/</link>

            <description>This article explains how to read values from an XML variable using XQuery</description>

            <guid isPermaLink="true">http://www.sqlservercentral.com/articles/Miscellaneous/2996/</guid>

            <pubDate>Wed, 12 Mar 2008 23:45:02 GMT</pubDate>

        </item>

    </channel>

</rss>

Conclusions

This is yet another session that demonstrates an XML shaping example. We have seen different XML shaping requirements and their implementation in the previous sessions of the XML Workshop. This session explains the basics of generating an RSS 2.0 feed using TSQL keyword FOR XML PATH.

Resources:

TSQLRssFeedSample.xml
Total article views: 5956 | Views in the last 30 days: 36
 
Related Articles
ARTICLE

XML Workshop XX - Generating an RSS 2.0 Feed with TSQL(SQL server 2000)

SQL Server MVP Jacob Sebastian continues his very successful XML series with a foray into SQL Server...

FORUM

Generating Online Description for Stored Procedures

Generating Online Description for Stored Procedures

ARTICLE

XML Workshop XXII - A TSQL RSS Library

RSS feeds are becoming almost required for any type of data publication. SQL Server MVP Jacob Sebast...

ARTICLE

XML Workshop 26 – SELECT * FROM XML

This article presents a generic function that makes it easy to query XML documents

FORUM

How to generate the snapshot just for one article

How to generate the snapshot just for one article

Tags
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones