Generating RSS in an SQL procedure

Structure RSS

An RSS document consists of two parts: a channel and articles.

Channel -  title RSS-tape.

Elements of channel 

The main information about the RSS feed is provided by three necessary channel elements:

RSS feed elements can also contain up to 15 additional elements (category, copyright, etc. ).

Elements of articles item

An RSS feed can consist of many articles. The main components of the article are the title and description.

In addition to the above, there are 5 additional elements that can be added to the article.

To create RSS, you need to create a new data type in the database - a custom data type (User-Defined Table Type). 

Custom data type - RSSItemType

RSSItemType  - actually repeats the RSS feed structure.

DROP TYPE if exists [dbo].[RSSItemType] ;
go

CREATE TYPE [dbo].[RSSItemType]
AS TABLE(
 titleArticle		 nvarchar(max) NULL
,descriptionArticle  nvarchar(max) NULL
,authorArticle	   nvarchar(max) NULL
,linkArticle		 nvarchar(1024) NULL
,pubDateArticle	   nvarchar(50)
,[guid]		   nvarchar(1024) NULL

)

Limitations.

Inability to change the structure of a custom type after creation!

Deletion is possible only if there are no objects in the database that refer to this type (columns, functions, procedures, or triggers).

Working with objects with this data type is similar to working with table variables. 

-- Creation
DECLARE @TestRSSItemTable RSSItemType

-- Data insertion
INSERT INTO @TestRSSItemTable (  titleArticle ,descriptionArticle ,authorArticle ,linkArticle , [guid]	, pubDateArticle)
SELECT  'RSS Tutorial', 'New RSS tutorial on w3ii', 'hege@mail.ru', 'http://www.w3ii.com/xml/xml_rss.html', 'http://www.w3ii.com/xml/xml_rss.html'
        , left( datename( dw, getutcdate() ), 3 ) + ', ' + convert( varchar(20), getutcdate(), 113 ) + ' GMT'
UNION
SELECT  'XML Tutorial', 'New XML tutorial on w3ii', 'hege2@mail.ru', 'http://www.w3ii.com/xml', 'http://www.w3ii.com/xml'
        , left( datename( dw, getutcdate() ), 3 ) + ', ' + convert( varchar(20), getutcdate(), 113 ) + ' GMT'
UNION
SELECT  'XML Tutorial test1', 'New XML tutorial on w3ii test1', 'hegetest1@mail.ru', 'http://www.w3ii.com/xml2', 'http://www.w3ii.com/xml2'
        , left( datename( dw, getutcdate() ), 3 ) + ', ' + convert( varchar(20), getutcdate(), 113 ) + ' GMT'

Generation procedure RSS - xml_getRSS 

The procedure receives a variable of the type as an input parameter RSSItemType, containing data for the RSS feed and channel parameters (title, link, description, language).

As a result, returns a line in XML format with the received data.

Error handling.

The procedure returns an error if the input variable contains no data.

Error text: Error! No data. Execution aborted.

Procedure call.

EXEC [dbo].[xml_getRSS] @TestRSSItemTable ,'w3ii Home Page title' ,'http://www.w3ii.com' ,'Free web building tutorials' ,'en-us'

The result of the procedure in XML format.

<!--?xml version="1.0" encoding="UTF-8" ?-->
<rss version="2.0">
<channel>
<title>w3ii Home Page title</title>
<link>http://www.w3ii.com
<description>Free web building tutorials</description>
<language>en-us</language>
<item>
<title>RSS Tutorial</title>
<description>New RSS tutorial on w3ii</description>
<author>hege@mail.ru</author>
<link>http://www.w3ii.com/xml/xml_rss.html
<guid>http://www.w3ii.com/xml/xml_rss.html</guid>
<pubdate>Fri, 04 Sep 2020 11:35:58 GMT</pubdate>
</item>
<item>
<title>XML Tutorial</title>
<description>New XML tutorial on w3ii</description>
<author>hege2@mail.ru</author>
<link>http://www.w3ii.com/xml
<guid>http://www.w3ii.com/xml</guid>
<pubdate>Fri, 04 Sep 2020 11:35:58 GMT</pubdate>
</item>
<item>
<title>XML Tutorial test1</title>
<description>New XML tutorial on w3ii test1</description>
<author>hegetest1@mail.ru</author>
<link>http://www.w3ii.com/xml2
<guid>http://www.w3ii.com/xml2</guid>
<pubdate>Fri, 04 Sep 2020 11:35:58 GMT</pubdate>
</item>
</channel>
</rss>

The result can be checked by the service W3C Feed Validation Service, for Atom and RSS - https://validator.w3.org/feed/#validate_by_input

Text of the procedure: 

ALTER PROCEDURE [dbo].[xml_getRSS]
 @RSSItemData		 RSSItemType READONLY
,@titleChannel		 nvarchar(max)  NULL
,@linkChannel		 nvarchar(1024) NULL
,@descriptionChannel nvarchar(max)  NULL
,@languageChannel	 nvarchar(max)  = 'en-us'

-- This procedure generates data for the RSS news feed.
-- Input data: a variable of a custom data type (User-Defined Table Types)  – the structure of RSS feeds + channel parameter

-- call --
/* DECLARE @TestRSSItemTable RSSItemType
INSERT INTO @TestRSSItemTable (  titleArticle ,descriptionArticle ,authorArticle ,linkArticle , [guid]	, pubDateArticle)
SELECT  'RSS Tutorial', 'New RSS tutorial on w3ii', 'hege@mail.ru', 'http://www.w3ii.com/xml/xml_rss.html', 'http://www.w3ii.com/xml/xml_rss.html'
        , left( datename( dw, getutcdate() ), 3 ) + ', ' + convert( varchar(20), getutcdate(), 113 ) + ' GMT'
UNION
SELECT  'XML Tutorial', 'New XML tutorial on w3ii', 'hege2@mail.ru', 'http://www.w3ii.com/xml', 'http://www.w3ii.com/xml'
        , left( datename( dw, getutcdate() ), 3 ) + ', ' + convert( varchar(20), getutcdate(), 113 ) + ' GMT'
UNION
SELECT  'XML Tutorial test1', 'New XML tutorial on w3ii test1', 'hegetest1@mail.ru', 'http://www.w3ii.com/xml', 'http://www.w3ii.com/xml'
        , left( datename( dw, getutcdate() ), 3 ) + ', ' + convert( varchar(20), getutcdate(), 113 ) + ' GMT'

EXEC dbo.xml_getRSS @TestRSSItemTable ,'w3ii Home Page title' ,'http://www.w3ii.com' ,'Free web building tutorials' ,'en-us'
*/
AS
BEGIN

	DECLARE @result  nvarchar(max)
	SET @result = N'' -- result of procedure
	SET @result =  @result + N''+char(10)+''+char(10)
-------------------------------------------------------------
	DECLARE @cnt int = 0
	SELECT  @cnt = COUNT(*) FROM @RSSItemData
	SELECT  @cnt = ISNULL( @cnt, 0)

	IF @cnt = 0
	BEGIN
		RAISERROR (N'Error! No data. Execution aborted.', 11,1)
		RETURN
	END
-------------------------------------------------------------
-- Channel
	SET @result =  @result + ''+char(10) -- откр.
	SET @result =  @result + ''		+ ISNULL( @titleChannel, '')		+''+char(10)
	                       + ''		+ ISNULL( @linkChannel, '')			+''+char(10)
	                       + ''+ ISNULL( @descriptionChannel, '')	+''+char(10)
	                       + ''	+ ISNULL( @languageChannel, '')		+''+char(10)

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Articles
	DECLARE @tmp_titleArticle nvarchar(max), @tmp_descriptionArticle nvarchar(max), @tmp_authorArticle nvarchar(max), @tmp_linkArticle nvarchar(max), @tmp_pubDateArticle nvarchar(max), @tmp_guid nvarchar(max)
	DECLARE tmp_CursorArticles CURSOR --Declaring the cursor
	FORWARD_ONLY
	FOR
		SELECT titleArticle ,descriptionArticle ,authorArticle ,linkArticle,pubDateArticle, [guid]
		FROM  @RSSItemData Articles
		ORDER BY titleArticle
	OPEN tmp_CursorArticles;

	FETCH NEXT FROM tmp_CursorArticles
	INTO @tmp_titleArticle, @tmp_descriptionArticle, @tmp_authorArticle, @tmp_linkArticle, @tmp_pubDateArticle ,@tmp_guid ; -- Select the first line
	WHILE @@FETCH_STATUS = 0  -- Iterate through the lines in the loop
	BEGIN
		SET @result =  @result + ''+char(10)

		SET @result =  @result + ''		+ ISNULL( @tmp_titleArticle, '')		+''			+char(10)
							   + ''+ ISNULL( @tmp_descriptionArticle, '')	+''	+char(10)
							   + ''		+ ISNULL( @tmp_authorArticle, '')		+''		+char(10)
							   + ''		+ ISNULL( @tmp_linkArticle, '')			+''			+char(10)
							   + ''		+ ISNULL( @tmp_guid, '')				+''			+char(10)
							   + ''	+ ISNULL( @tmp_pubDateArticle, '')		+''		+char(10)

		SET @result =  @result + ''+char(10)


	FETCH NEXT FROM tmp_CursorArticles
	INTO @tmp_titleArticle, @tmp_descriptionArticle, @tmp_authorArticle, @tmp_linkArticle, @tmp_pubDateArticle ,@tmp_guid ;  -- Select the next line
	END; --  tmp_CursorArticles;
	CLOSE tmp_CursorArticles;
	DEALLOCATE tmp_CursorArticles;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
		SET @result =  @result + ''+char(10) -- close
-------------------------------------------------------------
	--
	SET @result =  @result + N''+char(10)
	-- output the result
	SELECT  @result

END -- PROCEDURE 

Additional reference

Specification RSS 2.0 - https://www.internet-technologies.ru/articles/specifikaciya-rss-2-0.html

News feed RSS -  https://htmlweb.ru/other/rss.php

XML RSS - https://www.w3bai.com/ru/xml/xml_rss.html

How to format an RFC822 date for RSS