Hello people,
All right with you ?
I was watching the last video of Fabricio Limawhere he wrote about the blogs he usually follows by feedly, an RSS reader. I found the idea cool and decided to create a small RSS reader running on SQL Server using CLR or cURL running with xp_cmdshell.
In this case, since the return of requests is longer than 4.000 characters in most cases, it is not possible to do so using OLE Automation, which has this limitation.
Because the RSS feed return is basically XML, you will need to have a basic understanding of handling and manipulating strings and XML files. To do so, visit my post SQL Server - How to read, import, and export data from XML files.
One thing worth mentioning is that the amount of records returned by each site is configurable, ie one site for returning the latest 50 posts and another site returning only the latest 10 posts (WordPress default). However, even if the RSS feed is limited to 10 records, you can navigate between feed pages (see below).
How to query WordPress RSS feeds using CLR
To perform this query using CLR (C #), simply use the Stored Procedure stpWs_Requiringwhere source code is available in the post Performing POST and GET requests using CLR (C #) in SQL Server.
If you don't know or want to know more about SQLCLR (C #) in SQL Server, check out these 2 links:
How to enable the use of CLR in your instance:
|
1 2 3 4 5 6 7 8 9 10 11 |
sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'clr enabled', 1 GO RECONFIGURE GO |
View source:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
CREATE PROCEDURE dbo.stpConsulta_Rss ( @Ds_URL VARCHAR(255) ) AS BEGIN DECLARE @Ds_Retorno_OUTPUT NVARCHAR(MAX), @Retorno_XML XML EXEC CLR.dbo.stpWs_Requisicao @Ds_Url = @Ds_URL, -- nvarchar(max) @Ds_Metodo = N'GET', -- nvarchar(max) @Ds_Parametros = N'', -- nvarchar(max) @Ds_Codificacao = N'UTF-8', -- nvarchar(max) @Ds_Retorno_OUTPUT = @Ds_Retorno_OUTPUT OUTPUT -- nvarchar(max) SET @Retorno_XML = REPLACE(@Ds_Retorno_OUTPUT, '<?xml version="1.0" encoding="UTF-8"?>', '') SELECT Dados.linhas.value('title[1]', 'varchar(max)') AS Ds_Titulo, Dados.linhas.value('link[1]', 'varchar(max)') AS Ds_Link, CONVERT(DATETIME, SUBSTRING(Dados.linhas.value('pubDate[1]', 'varchar(max)'), 6, 20)) AS Dt_Publicacao, Dados.linhas.query('for $i in category return concat($i/text()[1], ";")').value('.', 'varchar(max)') AS Ds_Categoria, Dados.linhas.value('description[1]', 'varchar(max)') AS Ds_Descricao FROM @Retorno_XML.nodes('/rss/channel/item') AS Dados(linhas) END |
How to query WordPress RSS feeds using xp_cmdshell
To perform this query using xp_cmdshell, we will need the cURL binary available on this link.
To be able to use the curl.exe binary without specifying the path, you must either copy it to the C: \ Windows \ System32 directory or enter the full path of curl.exe on your machine.
How to enable the use of xp_cmdshell in your instance:
|
1 2 3 4 5 6 7 8 9 10 11 |
sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO |
View source:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
CREATE PROCEDURE dbo.stpConsulta_Rss ( @Ds_URL VARCHAR(255) ) AS BEGIN DECLARE @Comando VARCHAR(4000) = 'curl.exe --fail --silent --show-error --output "C:\Teste\output.xml" ' + @Ds_URL + ' | type "C:\Teste\output.xml"', @Conteudo VARCHAR(MAX) = '', @Retorno_XML XML, @Linha_Inicio INT, @Linha_Fim INT IF (OBJECT_ID('tempdb..#Retorno') IS NOT NULL) DROP TABLE #Retorno CREATE TABLE #Retorno ( Linha INT IDENTITY(1, 1), Ds_Saida VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS ) INSERT INTO #Retorno EXEC master.dbo.xp_cmdshell @Comando SELECT @Linha_Inicio = Linha FROM #Retorno WHERE Ds_Saida LIKE '<rss version%' SELECT @Linha_Fim = Linha FROM #Retorno WHERE Ds_Saida LIKE '</rss>%' DELETE FROM #Retorno WHERE Linha < @Linha_Inicio OR Linha > @Linha_Fim -- Tratamento de acentuação UPDATE #Retorno SET Ds_Saida = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( Ds_Saida, 'ÔÇÿ', '"'), 'ÔÇÖ', '"'), 'ÔÇô', '-'), '+ü', 'Á'), '+â', 'Ã'), '+é', 'Â'), '+Ç', 'À'), '+ë', 'É'), '+ê', 'È'), '+è', 'Ê'), '+ì', 'Í'), '+Ä', 'Î'), '+î', 'Ì'), '+ô', 'Ó'), '+ò', 'Õ'), '+Æ', 'Ò'), '+ö', 'Ô'), '+Ü', 'Ú'), '+Ö', 'Ù'), '+ø', 'Û'), '+ç', 'Ç'), '+í', 'á'), '+ú', 'ã'), '+ó', 'â'), '+á', 'à'), '+ó', 'â'), '+®', 'é'), '+¿', 'è'), '+¬', 'ê'), '+¡', 'í'), '+«', 'î'), '+¼', 'ì'), '+¦', 'ó'), '+Á', 'õ'), '+¦', 'ú'), '++', 'û'), '+º', 'ç') COLLATE SQL_Latin1_General_CP1_CS_AS SELECT @Conteudo += ISNULL(Ds_Saida, '') FROM #Retorno SET @Retorno_XML = REPLACE(@Conteudo, '<?xml version="1.0" encoding="UTF-8"?>', '') SELECT Dados.linhas.value('title[1]', 'varchar(max)') AS Ds_Titulo, Dados.linhas.value('link[1]', 'varchar(max)') AS Ds_Link, CONVERT(DATETIME, SUBSTRING(Dados.linhas.value('pubDate[1]', 'varchar(max)'), 6, 20)) AS Dt_Publicacao, Dados.linhas.query('for $i in category return concat($i/text()[1], ";")').value('.', 'varchar(max)') AS Ds_Categoria, Dados.linhas.value('description[1]', 'varchar(max)') AS Ds_Descricao FROM @Retorno_XML.nodes('/rss/channel/item') AS Dados(linhas) END |
Usage example (It is the same for the two SP's - the CLR and xp_cmdshell):
Querying SQL Server Updates
Want to stay on top of SQL Server updates and know when a Service Pack, Cumulative Update, or new version was released?
Meet the blog https://blogs.msdn.microsoft.com/sqlreleaseservices and follow your feed.
How to create an RSS feed reader in SQL Server
Now I will demonstrate how to create an RSS feed reader, so that it will look at all the posts from the sites you want to follow and send you weekly updates with the new posts. You can customize this reader at will, you can change the periodicity of alerts, creating a category option and sending email, separating posts by categories, etc. Be creative.
In order to create the RSS feed reader below, you will need 2 prerequisites:
- StpExporta_Table_HTML_Output Procedure, available in post How to export data from a SQL Server table to HTML.
- Database Mail configured. If not, see how to do this in the post SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail).
Table creation and configuration
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
IF (OBJECT_ID('dbo.Sites_RSS') IS NOT NULL) DROP TABLE dbo.Sites_RSS CREATE TABLE dbo.Sites_RSS ( Id INT IDENTITY(1, 1) NOT NULL, Ds_Url VARCHAR(255) ) INSERT INTO dbo.Sites_RSS ( Ds_Url ) VALUES ( 'https://dirceuresende.com/blog/feed' ), ( 'https://pessoalex.wordpress.com/feed/' ), ( 'https://demetriosilva.wordpress.com/feed' ), ( 'https://caiosamante.wordpress.com/feed' ), ( 'http://edvaldocastro.com/feed' ), ( 'https://blogs.msdn.microsoft.com/fcatae/feed' ), ( 'http://www.fabriciolima.net/feed' ), ( 'https://gustavomaiaaguiar.wordpress.com/feed' ), ( 'https://pedrogalvaojunior.wordpress.com/feed' ), ( 'https://leka.com.br/feed' ), ( 'https://blogdojamal.wordpress.com/feed' ), ( 'http://crespidb.com/feed' ), ( 'http://blog.sqlauthority.com/feed' ), ( 'https://sqlfromhell.wordpress.com/feed' ), ( 'https://sqlgo.wordpress.com/feed' ), ( 'http://www.tiagoneves.net/blog/feed' ), ( 'http://www.vssti.com.br/blog/feed' ) IF (OBJECT_ID('dbo.Conteudo_RSS') IS NOT NULL) DROP TABLE dbo.Conteudo_RSS CREATE TABLE dbo.Conteudo_RSS ( Id INT IDENTITY(1, 1) NOT NULL, Ds_Url VARCHAR(500), Ds_Titulo VARCHAR(500), Ds_Link VARCHAR(500), Dt_Publicacao DATETIME, Ds_Categorias VARCHAR(MAX), Ds_Conteudo VARCHAR(MAX), Fl_Enviado BIT ) -- Executa a SP para buscar os posts, -- atualizar no banco -- e enviar as novidades por e-mail EXEC dbo.stpLeitor_Feeds_RSS |
RSS reader source code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 |
CREATE PROCEDURE dbo.stpLeitor_Feeds_RSS AS BEGIN --------------------------------------------------------------------------------- -- ATUALIZA O CONTEÚDO --------------------------------------------------------------------------------- DECLARE @Contador INT = 1, @TotalSites INT = (SELECT COUNT(*) FROM dbo.Sites_RSS), @Url VARCHAR(500), @Ultima_Data DATETIME IF (OBJECT_ID('tempdb..#Sites') IS NOT NULL) DROP TABLE #Sites SELECT ROW_NUMBER() OVER(ORDER BY Ds_Url) AS Ranking, Ds_Url INTO #Sites FROM dbo.Sites_RSS WITH(NOLOCK) IF (OBJECT_ID('tempdb..#Conteudo') IS NOT NULL) DROP TABLE #Conteudo CREATE TABLE #Conteudo ( Id INT IDENTITY(1, 1) NOT NULL, Ds_Titulo VARCHAR(500), Ds_Link VARCHAR(500), Dt_Publicacao DATETIME, Ds_Categorias VARCHAR(MAX), Ds_Conteudo VARCHAR(MAX) ) WHILE(@Contador <= @TotalSites) BEGIN SELECT @Url = Ds_Url FROM #Sites WHERE Ranking = @Contador TRUNCATE TABLE #Conteudo INSERT INTO #Conteudo EXEC dbo.stpConsulta_Rss @Ds_URL = @Url -- varchar(255) SELECT @Ultima_Data = MAX(Dt_Publicacao) FROM dbo.Conteudo_RSS WITH(NOLOCK) WHERE Ds_Url = @Url INSERT INTO dbo.Conteudo_RSS ( Ds_Url, Ds_Titulo, Ds_Link, Dt_Publicacao, Ds_Categorias, Ds_Conteudo, Fl_Enviado ) SELECT @Url, Ds_Titulo, Ds_Link, Dt_Publicacao, Ds_Categorias, Ds_Conteudo, 0 AS Fl_Enviado FROM #Conteudo WHERE Dt_Publicacao > ISNULL(@Ultima_Data, '1900-01-01') SET @Contador += 1 END --------------------------------------------------------------------------------- -- ENVIA OS NOVOS POSTS --------------------------------------------------------------------------------- -- Não envia posts com mais de 7 dias UPDATE dbo.Conteudo_RSS SET Fl_Enviado = 1 WHERE Dt_Publicacao < DATEADD(DAY, -7, CONVERT(DATE, GETDATE())) AND Fl_Enviado = 0 IF (OBJECT_ID('tempdb..##Leitor_RSS') IS NOT NULL) DROP TABLE ##Leitor_RSS SELECT Ds_Url AS [Fonte], Ds_Titulo AS [Titulo], Dt_Publicacao AS [Publicação], Ds_Link AS [URL] INTO ##Leitor_RSS FROM dbo.Conteudo_RSS WHERE Fl_Enviado = 0 CREATE CLUSTERED INDEX SK01_Leitir_RSS ON ##Leitor_RSS([Publicação] DESC) IF ((SELECT COUNT(*) FROM ##Leitor_RSS) > 0) BEGIN DECLARE @Ds_Saida VARCHAR(MAX); EXEC dbo.stpExporta_Tabela_HTML_Output @Ds_Tabela = '##Leitor_RSS' , -- varchar(max) @Fl_Aplica_Estilo_Padrao = 1 , -- bit @Ds_Saida = @Ds_Saida OUTPUT -- varchar(max) EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileEnvioEmail' , -- sysname @subject = N'Leitor RSS - Atualização Semanal' , -- nvarchar(255) @body = @Ds_Saida , -- nvarchar(max) @body_format = 'HTML' UPDATE dbo.Conteudo_RSS SET Fl_Enviado = 1 WHERE Fl_Enviado = 0 SELECT * FROM ##Leitor_RSS END END |
Example of Email Sent
That's it folks!
I hope you enjoyed.
Regards and see you next post.
Dirceu,
Good Afternoon,
Very good the content of your post, is helping me a lot. But I have a problem that I can't solve.
I have several WS SOAP to access and I intend to use SQL Server to access them. The language I use for the application does not have the ability to access WS SOAP.
I am testing CURL on Windows, when I access from the WS command line CURL has to be run in the same directory as the request XML. This is working and the result is expected.
My problem is as follows, when I am using CURL with xp_cmdshell in a Stored Procedure, as I indicate what XML information I will use as a request since I will be on the DB server there is no way to execute it in a given directory.
Thanks a lot if you can give me a solution.
A big hug.