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

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

By Jacob Sebastian,

Introduction

In XML Workshop XVIII, we have seen how to generate an RSS 2.0 feed from TSQL. The session explained the feed generation process step by step and used FOR XML PATH to generate a valid RSS 2.0 feed.

FOR XML PATH is a very powerful keyword that provides a great deal of control over the structure of the XML document being generated. We could generate very complex XML structures by using FOR XML with PATH. PATH is a new keyword introduced with SQL Server 2005 and hence it is not available in SQL Server 2000. The focus of this session is to write the TSQL code for SQL Server 2000 that generates a valid RSS 2.0 feed. Since PATH is not available in SQL Server 2000, we will use FOR XML with EXPLICIT to generate the feed. In the previous sessions of XML Workshop, we have had a good look into TSQL keyword FOR XML along with AUTO, RAW, PATH and EXPLICIT.

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.

<rss version="2.0">

  <channel>

    <title>Welcome to XML Workshop</title>

    <link>http://www.sqlserverandxml.com/...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>

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

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

    <ttl>100</ttl>

    <image>

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

      <title>Welcome to XML Workshop</title>

      <link>http://www.sqlserverandxml.com/...central.html</link>

      <width>144</width>

      <height>22</height>

    </image>

    <item>

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

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

      <description>

        A short article that explains how to generate XML output

        with TSQL keyword FOR XML

      </description>

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

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

    </item>

    <item>

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

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

      <description>

        This article explains how to read values from an XML variable

        using XQuery

      </description>

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

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

    </item>

  </channel>

</rss>

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.

IF OBJECT_ID('channel') IS NOT NULL DROP TABLE Channel

GO

 

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

 

IF OBJECT_ID('Articles') IS NOT NULL DROP TABLE Articles

GO

 

CREATE TABLE Articles(

    ArticleID INT IDENTITY(1,1),

    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/...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/...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/...2982.asp',

    'A short article that explains how to generate XML output

    with TSQL keyword FOR XML',

    'http://www.sqlservercentral.com/...2982.asp',

    '2008-03-12 23:45:02'

UNION ALL

SELECT

    'XML Workshop II - Reading values from XML variables',

    'http://www.sqlservercentral.com/...2996/',

    'This article explains how to read values from an XML variable

    using XQuery',

    'http://www.sqlservercentral.com/...2996/',

    '2008-03-12 23:45:02'

Generating the feed

Let us start writing the TSQL code to generate the feed. Let us break the task into different steps and attempt one step at a time.

Step 1

Let us generate the root element at this step. The root element of an RSS 2.0 feed is the rss element.

SELECT

    1 AS Tag,

    NULL AS Parent,

    '2.0' AS 'rss!1!version'

FOR XML EXPLICIT

<rss version="2.0" />

Step 2

Let us generate the channel element at this step. The channel element is little complicated because it has a number of child elements and some of the child elements have their children too. So at this step, let us just create a basic declaration of the channel element.

SELECT

    1 AS Tag,

    NULL AS Parent,

    '2.0' AS 'rss!1!version',

    NULL AS 'channel!2!title!element'

UNION ALL

SELECT

    2 AS Tag,

    1 AS Parent,

    NULL,

    Title

FROM channel

 

FOR XML EXPLICIT

<rss version="2.0">

  <channel>

    <title>Welcome to XML Workshop</title>

  </channel>

</rss>

Step 3

Let us enhance the code a little more so that it includes all the child elements of channel.

SELECT

    1 AS Tag,

    NULL AS Parent,

    '2.0' AS 'rss!1!version',

    NULL AS 'channel!2!title!element',

    NULL AS 'channel!2!link!element',

    NULL AS 'channel!2!description!element',

    NULL AS 'channel!2!webMaster!element',

    NULL AS 'channel!2!language!element',

    NULL AS 'channel!2!copyright!element',

    NULL AS 'channel!2!lastBuildDate!element',

    NULL AS 'channel!2!ttl!element'

UNION ALL

SELECT

    2 AS Tag,

    1 AS Parent,

    NULL,

    Title ,

    Link,

    Description,

    WebMaster,

    Language,

    CopyRight,

    LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +

                STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),

    ttl

FROM channel

 

FOR XML EXPLICIT

<rss version="2.0">

  <channel>

    <title>Welcome to XML Workshop</title>

    <link>http://www.sqlserverandxml.com/...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>

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

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

    <ttl>100</ttl>

  </channel>

</rss>

Step 4

The structure of channel element is little complicated. One of its child element, image has other child elements too. This leads us to generate an additional level in the XML hierarchy. Lets us write the code to generate this structure.

SELECT

    1 AS Tag,

    NULL AS Parent,

    '2.0' AS 'rss!1!version',

    NULL AS 'channel!2!title!element',

    NULL AS 'channel!2!link!element',

    NULL AS 'channel!2!description!element',

    NULL AS 'channel!2!webMaster!element',

    NULL AS 'channel!2!language!element',

    NULL AS 'channel!2!copyright!element',

    NULL AS 'channel!2!lastBuildDate!element',

    NULL AS 'channel!2!ttl!element',

    NULL AS 'image!3!url!element',

    NULL AS 'image!3!title!element',

    NULL AS 'image!3!link!element',

    NULL AS 'image!3!width!element',

    NULL AS 'image!3!height!element'

UNION ALL

SELECT

    2 AS Tag,

    1 AS Parent,

    NULL,

    Title ,

    Link,

    Description,

    WebMaster,

    Language,

    CopyRight,

    LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +

                STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),

    ttl,

    NULL, NULL, NULL, NULL, NULL

FROM channel

UNION ALL

SELECT

    3 AS Tag,

    2 AS Parent,

    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

    ImageUrl,

    ImageTitle,

    ImageLink,

    ImageWidth,

    ImageHeight

FROM channel

FOR XML EXPLICIT

<rss version="2.0">

  <channel>

    <title>Welcome to XML Workshop</title>

    <link>http://www.sqlserverandxml.com/...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>

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

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

    <ttl>100</ttl>

    <image>

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

      <title>Welcome to XML Workshop</title>

      <link>http://www.sqlserverandxml.com/...central.html</link>

      <width>144</width>

      <height>22</height>

    </image>

  </channel>

</rss>

Step 5

We are done with the channel element. Let us move to the item element. Let us do it in two steps. First let us see if we can correctly generate the item elements with just the title information.

SELECT

    1 AS Tag,

    NULL AS Parent,

    '2.0' AS 'rss!1!version',

    NULL AS 'channel!2!title!element',

    NULL AS 'channel!2!link!element',

    NULL AS 'channel!2!description!element',

    NULL AS 'channel!2!webMaster!element',

    NULL AS 'channel!2!language!element',

    NULL AS 'channel!2!copyright!element',

    NULL AS 'channel!2!lastBuildDate!element',

    NULL AS 'channel!2!ttl!element',

    NULL AS 'image!3!url!element',

    NULL AS 'image!3!title!element',

    NULL AS 'image!3!link!element',

    NULL AS 'image!3!width!element',

    NULL AS 'image!3!height!element',

    NULL AS 'item!4!title!element'

UNION ALL

SELECT

    2 AS Tag,

    1 AS Parent,

    NULL,

    Title ,

    Link,

    Description,

    WebMaster,

    Language,

    CopyRight,

    LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +

                STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),

    ttl,

    NULL, NULL, NULL, NULL, NULL,

    NULL

FROM channel

UNION ALL

SELECT

    3 AS Tag,

    2 AS Parent,

    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

    ImageUrl,

    ImageTitle,

    ImageLink,

    ImageWidth,

    ImageHeight,

    NULL

FROM channel

UNION ALL

SELECT

    4 AS Tag,

    2 AS Parent,

    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

    NULL, NULL, NULL, NULL, NULL,

    title

FROM Articles

FOR XML EXPLICIT

<rss version="2.0">

  <channel>

    <title>Welcome to XML Workshop</title>

    <link>http://www.sqlserverandxml.com/...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>

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

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

    <ttl>100</ttl>

    <image>

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

      <title>Welcome to XML Workshop</title>

      <link>http://www.sqlserverandxml.com/...central.html</link>

      <width>144</width>

      <height>22</height>

    </image>

    <item>

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

    </item>

    <item>

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

    </item>

  </channel>

</rss>

Step 6

It looks like we are getting there. Let us write the query to generate the other elements too.

SELECT

    1 AS Tag,

    NULL AS Parent,

    '2.0' AS 'rss!1!version',

    NULL AS 'channel!2!title!element',

    NULL AS 'channel!2!link!element',

    NULL AS 'channel!2!description!element',

    NULL AS 'channel!2!webMaster!element',

    NULL AS 'channel!2!language!element',

    NULL AS 'channel!2!copyright!element',

    NULL AS 'channel!2!lastBuildDate!element',

    NULL AS 'channel!2!ttl!element',

    NULL AS 'image!3!url!element',

    NULL AS 'image!3!title!element',

    NULL AS 'image!3!link!element',

    NULL AS 'image!3!width!element',

    NULL AS 'image!3!height!element',

    NULL AS 'item!4!title!element',

    NULL AS 'item!4!link!element',

    NULL AS 'item!4!description!element',

    NULL AS 'item!4!guid!element',

    NULL AS 'item!4!pubDate!element'

UNION ALL

SELECT

    2 AS Tag,

    1 AS Parent,

    NULL,

    Title ,

    Link,

    Description,

    WebMaster,

    Language,

    CopyRight,

    LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +

                STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),

    ttl,

    NULL, NULL, NULL, NULL, NULL,

    NULL, NULL, NULL, NULL, NULL

FROM channel

UNION ALL

SELECT

    3 AS Tag,

    2 AS Parent,

    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

    ImageUrl,

    ImageTitle,

    ImageLink,

    ImageWidth,

    ImageHeight,

    NULL, NULL, NULL, NULL, NULL

FROM channel

UNION ALL

SELECT

    4 AS Tag,

    2 AS Parent,

    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

    NULL, NULL, NULL, NULL, NULL,

    title,

    Link,

    Description,

    Guid,

    LEFT(DATENAME(dw, PubDate),3) + ', ' +

                        STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT')

FROM Articles

FOR XML EXPLICIT

<rss version="2.0">

  <channel>

    <title>Welcome to XML Workshop</title>

    <link>http://www.sqlserverandxml.com/...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>

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

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

    <ttl>100</ttl>

    <image>

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

      <title>Welcome to XML Workshop</title>

      <link>http://www.sqlserverandxml.com/...central.html</link>

      <width>144</width>

      <height>22</height>

    </image>

    <item>

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

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

      <description>

        A short article that explains how to generate XML output

        with TSQL keyword FOR XML

      </description>

      <guid>http://www.sqlservercentral.com/...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/...2996/</link>

      <description>

        This article explains how to read values from an XML

        variable using XQuery

      </description>

      <guid>http://www.sqlservercentral.com/...2996/</guid>

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

    </item>

  </channel>

</rss>

Step 7

Well, we are almost done. The only remaining task is to add the attribute isPermalink with each item element. Let us try to add that.

SELECT

    1 AS Tag,

    NULL AS Parent,

    '2.0' AS 'rss!1!version',

    NULL AS 'channel!2!title!element',

    NULL AS 'channel!2!link!element',

    NULL AS 'channel!2!description!element',

    NULL AS 'channel!2!webMaster!element',

    NULL AS 'channel!2!language!element',

    NULL AS 'channel!2!copyright!element',

    NULL AS 'channel!2!lastBuildDate!element',

    NULL AS 'channel!2!ttl!element',

    NULL AS 'image!3!url!element',

    NULL AS 'image!3!title!element',

    NULL AS 'image!3!link!element',

    NULL AS 'image!3!width!element',

    NULL AS 'image!3!height!element',

    NULL AS 'item!4!title!element',

    NULL AS 'item!4!link!element',

    NULL AS 'item!4!description!element',

    NULL AS 'item!4!pubDate!element',

    NULL AS 'guid!5!isPermaLink',

    NULL AS 'guid!5!!element'

UNION ALL

SELECT

    2 AS Tag,

    1 AS Parent,

    NULL,

    Title ,

    Link,

    Description,

    WebMaster,

    Language,

    CopyRight,

    LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +

                STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),

    ttl,

    NULL, NULL, NULL, NULL, NULL,

    NULL, NULL, NULL, NULL,

    NULL, NULL

FROM channel

UNION ALL

SELECT

    3 AS Tag,

    2 AS Parent,

    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

    ImageUrl,

    ImageTitle,

    ImageLink,

    ImageWidth,

    ImageHeight,

    NULL, NULL, NULL, NULL,

    NULL, NULL

FROM channel

UNION ALL

SELECT

    4 AS Tag,

    2 AS Parent,

    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

    NULL, NULL, NULL, NULL, NULL,

    title,

    Link,

    Description,

    LEFT(DATENAME(dw, PubDate),3) + ', ' +

                        STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT'),

    NULL, NULL

FROM Articles

UNION ALL

SELECT

    5 AS Tag,

    4 AS Parent,

    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

    NULL, NULL, NULL, NULL, NULL,

    NULL, NULL, NULL, NULL,

    'true',

    guid

FROM Articles

FOR XML EXPLICIT

<rss version="2.0">

  <channel>

    <title>Welcome to XML Workshop</title>

    <link>http://www.sqlserverandxml.com/...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>

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

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

    <ttl>100</ttl>

    <image>

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

      <title>Welcome to XML Workshop</title>

      <link>http://www.sqlserverandxml.com/...central.html</link>

      <width>144</width>

      <height>22</height>

    </image>

    <item>

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

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

      <description>

        A short article that explains how to generate XML output

        with TSQL keyword FOR XML

      </description>

      <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/...2996/</link>

      <description>

        This article explains how to read values from an XML variable

        using XQuery

      </description>

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

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

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

    </item>

  </channel>

</rss>

Step 8

We have a problem here. The isPermalink attribute should be generated for each item element. At present they appear with the last element only. The problem is with the physical order of the query result. We need to make sure that the isPermalink row appears along with the rows of each item. We need to add some sort of ordering logic to get this done. Here is the updated version of the query.

SELECT

    Tag,

    Parent,

    [rss!1!version],

    [channel!2!title!element],

    [channel!2!link!element],

    [channel!2!description!element],

    [channel!2!webMaster!element],

    [channel!2!language!element],

    [channel!2!copyright!element],

    [channel!2!lastBuildDate!element],

    [channel!2!ttl!element],

    [image!3!url!element],

    [image!3!title!element],

    [image!3!link!element],

    [image!3!width!element],

    [image!3!height!element],

    [item!4!title!element],

    [item!4!link!element],

    [item!4!description!element],

    [item!4!pubDate!element],

    [guid!5!isPermaLink],

    [guid!5!!element]

FROM (

    SELECT

        1 AS Tag,

        NULL AS Parent,

        '2.0' AS 'rss!1!version',

        NULL AS 'channel!2!title!element',

        NULL AS 'channel!2!link!element',

        NULL AS 'channel!2!description!element',

        NULL AS 'channel!2!webMaster!element',

        NULL AS 'channel!2!language!element',

        NULL AS 'channel!2!copyright!element',

        NULL AS 'channel!2!lastBuildDate!element',

        NULL AS 'channel!2!ttl!element',

        NULL AS 'image!3!url!element',

        NULL AS 'image!3!title!element',

        NULL AS 'image!3!link!element',

        NULL AS 'image!3!width!element',

        NULL AS 'image!3!height!element',

        NULL AS 'item!4!title!element',

        NULL AS 'item!4!link!element',

        NULL AS 'item!4!description!element',

        NULL AS 'item!4!pubDate!element',

        NULL AS 'guid!5!isPermaLink',

        NULL AS 'guid!5!!element',

        CAST(1 AS VARBINARY(4)) AS Sort

    UNION ALL

    SELECT

        2 AS Tag,

        1 AS Parent,

        NULL,

        Title ,

        Link,

        Description,

        WebMaster,

        Language,

        CopyRight,

        LEFT(DATENAME(dw, LastBuildDate),3) + ', ' +

                    STUFF(CONVERT(nvarchar,LastBuildDate,113),21,4,' GMT'),

        ttl,

        NULL, NULL, NULL, NULL, NULL,

        NULL, NULL, NULL, NULL,

        NULL, NULL,

        CAST(1 AS VARBINARY(4)) + CAST(2 AS VARBINARY(4))

    FROM channel

    UNION ALL

    SELECT

        3 AS Tag,

        2 AS Parent,

        NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

        ImageUrl,

        ImageTitle,

        ImageLink,

        ImageWidth,

        ImageHeight,

        NULL, NULL, NULL, NULL,

        NULL, NULL,

        CAST(1 AS VARBINARY(4)) + CAST(2 AS VARBINARY(4))

            + CAST(3 AS VARBINARY(4))

    FROM channel

    UNION ALL

    SELECT

        4 AS Tag,

        2 AS Parent,

        NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

        NULL, NULL, NULL, NULL, NULL,

        title,

        Link,

        Description,

        LEFT(DATENAME(dw, PubDate),3) + ', ' +

                            STUFF(CONVERT(nvarchar,PubDate,113),21,4,' GMT'),

        NULL, NULL,

        CAST(1 AS VARBINARY(4)) + CAST(2 AS VARBINARY(4))

            + CAST(3 AS VARBINARY(4))

            + CAST(ArticleID AS VARBINARY(4))

    FROM Articles

    UNION ALL

    SELECT

        5 AS Tag,

        4 AS Parent,

        NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

        NULL, NULL, NULL, NULL, NULL,

        NULL, NULL, NULL, NULL,

        'true',

        guid,

        CAST(1 AS VARBINARY(4)) + CAST(2 AS VARBINARY(4))

            + CAST(3 AS VARBINARY(4))

            + CAST(ArticleID AS VARBINARY(4))

            + CAST(ArticleID AS VARBINARY(4))

    FROM Articles

) a

ORDER BY SORT

FOR XML EXPLICIT

<rss version="2.0">

  <channel>

    <title>Welcome to XML Workshop</title>

    <link>http://www.sqlserverandxml.com/...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>

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

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

    <ttl>100</ttl>

    <image>

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

      <title>Welcome to XML Workshop</title>

      <link>http://www.sqlserverandxml.com/...central.html</link>

      <width>144</width>

      <height>22</height>

    </image>

    <item>

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

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

      <description>

        A short article that explains how to generate XML output

        with TSQL keyword FOR XML

      </description>

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

      <guid isPermaLink="true">

        http://www.sqlservercentral.com/...2982.asp

      </guid>

    </item>

    <item>

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

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

      <description>

        This article explains how to read values from an XML variable

        using XQuery

      </description>

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

      <guid isPermaLink="true">

        http://www.sqlservercentral.com/...2996/

      </guid>

    </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 EXPLICIT.

Total article views: 3957 | Views in the last 30 days: 3
 
Related Articles
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 IX - Mixed Types

Continuing on with his great series on XML, Jacob Sebastian looks at elements of mixed types.

SCRIPT

Generating combinations 'm elements from n-element set'

This procedure generates a dataset with combinations of elements_to_select taken from number_of_valu...

FORUM

How to add attribute from Parent element to child element's attribute using visual studio designer

How to add attribute from Parent element to child element's attribute using visual studio designer

SCRIPT

Generate Job Schedule Descriptions

A SELECT statement which includes the alpha description of each job's schedule

Tags
sql server 7    
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