Генерация RSS в SQL процедуре

Структура RSS

Документ RSS состоит их двух частей: канала и статей.

Канал - заголовок RSS-ленты.

Элементы канала channel

Основную информацию о RSS-ленте предоставляют три необходимых элемента канала:

Элемент	Описание
title	Имя канала, а также название службы. Должно ассоциироваться с названием сайта
link	URL на Web-сайт, с которым установлен канал связи
description	Фраза, описывающая канал
	Необязательные элементы
language	Язык, на котором написан канал

Также элементы RSS-канала могут содержать до 15 дополнительных элементов (category, copyright и т.п. ).

Элементы статей item

RSS-лента может состоять из множества статей. Основными составляющими статьи являются заголовок (title) и описание (description).

Элемент	Описание
title	Заголовок статьи
description	Резюме
	Необязательные элементы
author	Адрес электронной почты автора статьи
link	URL статьи
pubDate	Дата и время публикации, соответствующие RFC 822. Пример: Sun, 19 May 2002 15:21:36 GMT. Скрипт формирования поля – в примере вставки данных.
guid	Строка, однозначно определяющая публикацию. Пример: http://bikman.ru/2004/01/01/a.html

Кроме вышеперечисленных, существует еще 5 дополнительных элементов, которые могут добавляться к статье.

Для создания RSS необходимо создать в БД новый тип данных - пользовательского тип данных (User-Defined Table Type).

Пользовательский тип данных - RSSItemType

RSSItemType - фактически повторяет структуру RSS ленты.

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

)

Ограничения.

Невозможности поменять структуру пользовательского типа после создания!

Удаление возможно, только в том случае, если в БД отсутствуют объекты, ссылающиеся на этот тип (столбцы, функции, процедуры или триггеры).

Работа с объектами с таким типом данных аналогична работе с табличными переменными.

-- Создание
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/xml2', 'http://www.w3ii.com/xml2'
        , left( datename( dw, getutcdate() ), 3 ) + ', ' + convert( varchar(20), getutcdate(), 113 ) + ' GMT'

Процедура генерации RSS - xml_getRSS

Процедура получает в качестве входного параметра переменную типа RSSItemType, содержащую данные для RSS ленты и параметры канала (title, link, description, language).

В результате возвращает строку в формате XML с полученными данными.

Обработка ошибок.

Процедура возвращает ошибку, если входн. переменная не содержит данных.

Текст ошибки: Ошибка! Нет данных. Выполнение прервано.

Вызов процедуры.

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

Результат процедуры в формате XML.

<!--?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>

Результат можно проверить сервисом W3C Feed Validation Service, for Atom and RSS - https://validator.w3.org/feed/#validate_by_input

Текст процедуры:

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'

-- Проц. Генерирует данные для ленты новостей RSS.
-- Входные данные: переменная пользовательского тип данных (User-Defined Table Types)  – структура RSS ленты. + параметры канала

-- вызов --
/* 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'' -- рез. процедуры
	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'Ошибка! Нет данных. Выполнение прервано.', 11,1)
		RETURN
	END
-------------------------------------------------------------
-- Канал
	SET @result =  @result + ''+char(10) -- откр.
	SET @result =  @result + ''		+ ISNULL( @titleChannel, '')		+''+char(10)
	                       + ''		+ ISNULL( @linkChannel, '')			+''+char(10)
	                       + ''+ ISNULL( @descriptionChannel, '')	+''+char(10)
	                       + ''	+ ISNULL( @languageChannel, '')		+''+char(10)

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Статьи
	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 --Объявляем курсор
	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 ; -- Выбираем первую строку
	WHILE @@FETCH_STATUS = 0  -- Выполняем в цикле перебор строк
	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 ;  -- Выбираем следующую строку
	END; --  tmp_CursorArticles;
	CLOSE tmp_CursorArticles;
	DEALLOCATE tmp_CursorArticles;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
		SET @result =  @result + ''+char(10) -- закрыть
-------------------------------------------------------------
	--
	SET @result =  @result + N''+char(10)
	-- вывод результата
	SELECT  @result

END -- PROCEDURE

Дополнительные ссылки

Спецификация RSS 2.0 - https://www.internet-technologies.ru/articles/specifikaciya-rss-2-0.html

Ленты новостей 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

Falcon Space - функциальная веб-платформа разработки на узком стеке MS SQL/Bootstrap. Вводная по Falcon Space