Hola, chicos,
¿Estás bien?
Estaba viendo el último vídeo. Fabricio Lima, donde escribió sobre los blogs que sigue habitualmente a través de Feedly, un lector de RSS. Pensé que la idea era genial y decidí crear un pequeño lector de RSS que se ejecuta en SQL Server, usando CLR o cURL que se ejecuta con xp_cmdshell.
En este caso, como la devolución de solicitudes es superior a 4.000 caracteres en la mayoría de los casos, no es posible hacerlo utilizando OLE Automation, que tiene esta limitación.
Como el retorno de la fuente RSS es básicamente XML, necesitará tener conocimientos básicos sobre el tratamiento y manipulación de cadenas y archivos XML. Para ello visita mi post. SQL Server: cómo leer, importar y exportar datos desde archivos XML.
Una cosa que vale la pena mencionar es que la cantidad de registros devueltos por cada sitio es configurable, es decir, un sitio devuelve las últimas 50 publicaciones y otro sitio devuelve solo las últimas 10 publicaciones (estándar de WordPress). Sin embargo, incluso si el feed RSS está limitado a 10 registros, es posible navegar entre las páginas del feed (ver más abajo).
Cómo consultar feeds RSS de WordPress usando CLR
Para realizar esta consulta usando CLR (C#), simplemente use el Procedimiento almacenado stpWs_Requisicao, donde el código fuente está disponible en la publicación Realizar solicitudes POST y GET usando CLR (C#) en SQL Server.
Si no sabe o quiere saber más sobre SQLCLR (C#) en SQL Server, consulte estos 2 enlaces:
Cómo habilitar el uso de CLR en su instancia:
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GOVer código fuente:
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)
ENDCómo consultar feeds RSS de WordPress usando xp_cmdshell
Para realizar esta consulta usando xp_cmdshell, necesitaremos el binario cURL, disponible en este enlace.
Para poder utilizar el binario curl.exe sin tener que especificar la ruta, debe copiarlo al directorio C:\Windows\System32 o ingresar la ruta completa de curl.exe en su máquina.
Cómo habilitar el uso de xp_cmdshell en su instancia:
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GOVer código fuente:
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)
ENDEjemplo de uso (es el mismo para ambos SP: CLR y xp_cmdshell):
Consulta de actualizaciones de SQL Server
¿Quiere mantenerse actualizado con las actualizaciones de SQL Server y saber cuándo se lanzó un Service Pack, una actualización acumulativa o una nueva versión?
descubre el blog https://blogs.msdn.microsoft.com/sqlreleaseservices y sigue tu feed.
Cómo crear un lector de feeds RSS en SQL Server
Ahora te demostraré cómo crear un lector de feeds RSS, para que consulte todas las publicaciones de los sitios que deseas seguir y te envíe actualizaciones semanales con nuevas publicaciones. Puedes personalizar este lector como desees, puedes cambiar la frecuencia de las alertas, creando una opción de categoría al enviar el correo electrónico, separando publicaciones por categorías, etc. Sé creativo.
Para poder crear el lector de feeds RSS a continuación, necesitará 2 requisitos previos:
- Procedimiento stpExporta_Table_HTML_Output, disponible en el post Cómo exportar datos de una tabla de SQL Server a HTML.
- Correo de base de datos configurado. Si no, mira cómo hacerlo en el post. SQL Server – Cómo activar y configurar el correo de base de datos para enviar y monitorear correos electrónicos a través de la base de datos (sp_send_dbmail).
Creación y configuración de tablas.
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_RSSCódigo fuente del lector RSS
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
@recipients = 'dirceu.resende@yahoo.com.br' , -- varchar(max)
@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
ENDEjemplo del correo electrónico enviado
¡Eso es todo, amigos!
Espero que te haya gustado.
Un abrazo y nos vemos en el próximo post.
Comentários (0)
Carregando comentários…