SQL Server - Cómo consultar y consumir feeds RSS de WordPress usando CLR o xp_cmdshell (cURL)

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
GO

Ver 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)


END

Có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
GO

Ver 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)


END

Ejemplo 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_RSS

Có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 = '[email protected]' , -- 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

    
END

Ejemplo del correo electrónico enviado

¡Eso es todo, amigos!
Espero que te haya gustado.

Un abrazo y nos vemos en el próximo post.

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…