Генерация 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 с полученными данными.

Код процедуры - https://pastebin.com/2JLxxfgn

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

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

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

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

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 по ссылке на сайте

Используем подгружаемые файлы (страница /rsfiletypes).

CREATE PROCEDURE [dbo].[rs_rss_getFile]
	@urlParameters DictionaryParameter READONLY,
	@username nvarchar(32),
	@langID int = 0
AS
BEGIN
	-- процедура выдает некий файл исходя из параметров URL (содержимое файла может браться из настроек либо формироваться прямо в процедуре)
	/*как получать параметры из URL*/
	-- declare @s nvarchar(128)
	-- select @s = Value  from @urlParameters where [Key] = 'itemID'
    
-- Создание
DECLARE @items RSSItemType2

-- Вставка данных
INSERT INTO @items (  titleArticle ,descriptionArticle ,authorArticle ,linkArticle , pubDateArticle, guid)
select title, 
	replace(replace(replace(replace(dbo.as_stripHTML(shortDesc), ' ', ''), '&', ''), '—', ''), '–', ''), 
	'info@falconspace.ru (Falcon Space)', 'https://falconspace.ru/blog/' + code, created, isnull(guid, newID())
from as_articles where isPublish=1 
order by id desc

declare @key nvarchar(128) = 'rss_articles'
declare @val nvarchar(max) = ''
select top 1 @val = data from as_cache where cacheKey = @key and updated > dateadd(day, -5, getdate() ) order by id desc
if(isnull(@val, '') = '') begin 
   --- вызываем некую сложную процедуру ... и сохраняем в @res 
    declare @t table(value nvarchar(max))
    insert into @t(value)
    EXEC [dbo].[xml_getRSS] @items ,'Блог платформы Falcon Space' ,'https://falconspace.ru' ,'Статьи про веб-проекты, создание IT-продуктов' ,'ru-ru'
	select @val = value from @t
    
   delete from as_cache  where cacheKey = @key  -- удаляем старые копии
   insert into as_cache (updated, cacheKey, data) values(getdate(), @key, @val)
end 


    
	-- выдаем результат
	select top 1 1 Result, '' Msg, 
		@val Text,  -- тело файла 
		'application/rss+xml' MimeType   -- Mime тип (https://ru.wikipedia.org/wiki/%D0%A1%D0%BF%D0%B8%D1%81%D0%BE%D0%BA_MIME-%D1%82%D0%B8%D0%BF%D0%BE%D0%B2)
	
END

--ru 25.12.2022 12:54:13
--ru 25.12.2022 13:32:31
--ru 16.09.2025 15:15:47

В этом примере используется кеширование результата выгрузки (чтобы не генерировать по каждому запросу заново результат RSS).

Подробности про подгружаемые файлы

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

Спецификация 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

Страница-источник на сайте falconspace.ru