Olá pessoal,
Tudo bem com vocês ?
Estava assistindo o último vídeo do Fabricio Lima, onde ele escreveu sobre os blogs que ele costuma acompanhar pelo feedly, um leitor de RSS. Achei legal a ideia e resolvi criar um pequeno leitor de RSS rodando no SQL Server, utilizando o CLR ou cURL rodando com xp_cmdshell.
Neste caso, como o retorno das requisições é superior a 4.000 caracteres na maioria dos casos, não é possível fazer utilizando OLE Automation, que possui essa limitação.
Como o retorno do feed RSS é basicamente um XML, você precisará ter uma noção básica de tratamento e manipulação de strings e arquivos XML. Para isso, acesse o meu post SQL Server – Como ler, importar e exportar dados de arquivos XML.
Uma coisa que vale a pena ser citada, é que a quantidade de registros retornados por cada site é configurável, ou seja, um site por retornar os últimos 50 posts e um outro site retornar apenas os últimos 10 posts (padrão do WordPress). Entretanto, mesmo que o feed RSS seja limitado a 10 registros, é possível navegar entre as páginas do feed (veja mais abaixo).
Como consultar feeds RSS do WordPress utilizando CLR
Para realizar essa consulta utilizando o CLR (C#), basta utilizar a Stored Procedure stpWs_Requisicao, onde o código-fonte está disponível no post Realizando requisições POST e GET utilizando CLR (C#) no SQL Server.
Caso você não conheça ou queira conhecer mais sobre o SQLCLR (C#) no SQL Server, veja esses 2 links:
Como habilitar o uso do CLR na sua instância:
|
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 |
Visualizar código-fonte:
|
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 |
Como consultar feeds RSS do WordPress utilizando xp_cmdshell
Para realizar essa consulta utilizando o xp_cmdshell, vamos precisar do binário do cURL, disponível neste link.
Para que seja possível utilizar o binário do curl.exe sem precisar especificar o caminho, você deve copiá-lo para o diretório C:\Windows\System32 ou então coloque o caminho completo do curl.exe da sua máquina.
Como habilitar o uso do xp_cmdshell na sua instância:
|
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 |
Visualizar código-fonte:
|
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 |
Exemplo de uso (É o mesmo para as duas SP’s – do CLR e xp_cmdshell):
Consultando as atualizações do SQL Server
Quer ficar por dentro das atualizações do SQL Server e saber quando foi lançado algum Service Pack, Cumulative Update ou nova versão?
Conheça o blog https://blogs.msdn.microsoft.com/sqlreleaseservices e acompanhe o seu feed.
Como criar um leitor de feeds RSS no SQL Server
Agora vou demonstrar como criar um leitor de feeds RSS, de modo que ele consulte todos os posts dos sites que você quer acompanhar e lhe envie atualizações semanais com os novos posts. Você pode personalizar essa leitor à vontade, pode alterar a periodicidade dos alertas, criando uma opção de categoria e no envio do e-mail, separar os posts por categorias, etc.. Seja criativo.
Para que seja possível criar o leitor de feeds RSS abaixo, você precisará de 2 pré-requisitos:
- Procedure stpExporta_Tabela_HTML_Output, disponível no post Como exportar dados de uma tabela do SQL Server para HTML.
- Database Mail configurado. Caso não esteja, veja como fazer isso no post SQL Server – Como ativar e configurar o Database mail para enviar e monitorar e-mails pelo banco de dados (sp_send_dbmail).
Criação das tabelas e configuração
|
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 |
Código-fonte do leitor de RSS
|
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 |
Exemplo do e-mail enviado
É isso aí, pessoal!
Espero que tenham gostado.
Um abraço e até o próximo post.
Portuguese 
English
Dirceu,
Boa Tarde,
Muito bom o conteúdo de seu posto, esta me ajudando bastante. Mas estou com um problema que não consigo solucionar.
Tenho varios WS SOAP para acessar e pretendo usar o SQL Server para acessa-los. A linguagem que uso para a aplicação, não tem a capacidade de acesso aos WS SOAP.
Estou testando o CURL no Windows, quando faço acesso pela linha de comando do WS o CURL tem ser executado no mesmo diretório onde esta o XML do request. Isso esta funcionando e o resultado e o esperado.
Meu problema é o seguinte, quando estou usando o CURL com xp_cmdshell em de uma Stored Procedure, como indico que informação do XML vou usar como request sendo que estarei no servidor de BD não tem como executa-la em uma determinado diretório.
Agradeço muito se você poder me dar uma solução.
Um grande abraço.