CREATE FUNCTION GenerateRss20

(

    @ch XML,    -- Channel Information

    @itm XML    -- Item Information

)

RETURNS XML

AS

BEGIN

    -- This is the variable that will hold the result (RSS feed)

    DECLARE @rss XML

 

    /*

        To make the process easier, let us transform Channel and Item

        information to a virtual table using CTE.

    */

    ;WITH channel AS (

        SELECT

            c.value('Title[1]','VARCHAR(500)') AS Title,

            c.value('Link[1]','VARCHAR(500)') AS Link,

            c.value('Description[1]','VARCHAR(MAX)') AS Description,

            c.value('Webmaster[1]','VARCHAR(50)') AS Webmaster,

            c.value('Language[1]','VARCHAR(20)') AS Language,

            c.value('ImageUrl[1]','VARCHAR(500)') AS ImageUrl,

            c.value('ImageTitle[1]','VARCHAR(500)') AS ImageTitle,

            c.value('ImageLink[1]','VARCHAR(500)') AS ImageLink,

            c.value('ImageWidth[1]','INT') AS ImageWidth,

            c.value('ImageHeight[1]','INT') AS ImageHeight,

            c.value('CopyRight[1]','VARCHAR(100)') AS CopyRight,

            c.value('LastBuildDate[1]','DATETIME') AS LastBuildDate,

            c.value('Ttl[1]','INT') AS Ttl

        FROM @ch.nodes('/Channel') ch(c)

    ), items AS (

        SELECT

            i.value('Title[1]','VARCHAR(500)') AS Title,

            i.value('Link[1]','VARCHAR(500)') AS Link,

            i.value('Description[1]','VARCHAR(MAX)') AS Description,

            i.value('Guid[1]','VARCHAR(500)') AS Guid,

            i.value('PubDate[1]','DATETIME') AS PubDate

        FROM @itm.nodes('/Item/Items') itm(i)

    )

    /*

        Generate the RSS feed and assign to the local variable

    */

    SELECT @rss = (

        SELECT

            '2.0' AS '@version',

            (

                SELECT

                    Title AS title,

                    Link AS link,

                    Description AS description,

                    Webmaster AS webMaster,

                    ISNULL(Language, 'en-us') 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, ISNULL(LastBuildDate,GETDATE())),3) + ', ' +

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

                        AS lastBuildDate,

                    Ttl AS ttl,

                    (

                        SELECT

                            Title AS title,

                            Link AS link,

                            Description AS description,

                            CASE

                                WHEN ISNULL(guid, Link) IS NULL THEN NULL

                                ELSE 'true'

                            END AS 'guid/@isPermaLink',

                            ISNULL(Guid, Link) AS guid,

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

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

                                AS pubDate

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

                    )

                FROM channel

                FOR XML PATH('channel'), TYPE

            )

        FOR XML PATH('rss')

    )

    -- return the feed

    RETURN @rss

END