Hola, chicos,
¡Buenas noches!

En esta publicación, le demostraré cómo usar la nueva función de manejo de JSON en SQL Server 2016, que utiliza un procedimiento CLR (C#) para devolver información sobre su serie de televisión favorita. Esta es una forma interesante y divertida de aprender un poco más sobre estas dos poderosas herramientas de SQL Server.

Esta idea fue inspirada por la publicación. Consumiendo JSON con Procedimientos de Automatización Ole en SQL Server 2016, avisándote de tus episodios favoritos (Off-Topic), del blog de Reginaldo Silva y decidí demostrar esta solución usando CLR.

Si no sabes qué es CLR, infórmate accediendo al post Introducción a SQL CLR (Common Language Runtime) en SQL Server.

Crear el procedimiento almacenado para consultar la API web

Para consultar la API web de TVMaze.com (http://www.tvmaze.com/api), Reginaldo utilizó procedimientos de Automatización OLE, mientras que en este post usaré el procedimiento stpWs_Requisicao, escrito en C# para ser utilizado en SQLCLR.

Código fuente del Procedimiento stpWs_Requisicao:

using System.Data.SqlTypes;
using System.Net;
using System.IO;
using System.Text;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpWs_Requisicao(SqlString Ds_Url, SqlString Ds_Metodo, SqlString Ds_Parametros, SqlString Ds_Codificacao, out SqlString Ds_Retorno_OUTPUT)
    {

        var parametros = (Ds_Parametros.IsNull) ? "" : Ds_Parametros.Value;
        var metodo = (Ds_Metodo.IsNull) ? "GET" : Ds_Metodo.Value;
        var url = Ds_Url.Value;
        var feedData = string.Empty;
        var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value;

        if (string.IsNullOrEmpty(encoding))
            encoding = "UTF-8";

        var request = (HttpWebRequest) WebRequest.Create(url);
        request.Method = metodo;
        
        if (metodo == "POST" && parametros.Length > 0)
        {
            var data = parametros;
            var dataStream = Encoding.UTF8.GetBytes(data);

            request.ContentLength = dataStream.Length;

            using (var newStream = request.GetRequestStream())
            {
                newStream.Write(dataStream, 0, dataStream.Length);
                newStream.Close();
            }
        }


        using (var response = (HttpWebResponse) request.GetResponse())
        {
            using (var stream = response.GetResponseStream())
            {
                if (stream != null)
                {
                    using (var streamReader = new StreamReader(stream, Encoding.GetEncoding(encoding)))
                    {
                        feedData = streamReader.ReadToEnd();
                    }
                }
            }
        }

        Ds_Retorno_OUTPUT = feedData;

    }

};

Comprender la función JSON_VALUE en SQL Server 2016

Una vez que el procedimiento almacenado se haya creado y publicado en su base de datos, ahora puede crear su procedimiento de consulta en serie, utilizando el siguiente código, que también utiliza la nueva característica JSON_VALUE en SQL Server 2016, que le permite manipular cadenas JSON de forma nativa en la base de datos.

Ejemplos de uso de JSON_VALUE:

Creando el procedimiento almacenado para buscar series

Una vez que se haya creado el CLR stpWS_Requisicao y ya comprenda cómo funciona la nueva función JSON_VALUE, creemos el procedimiento almacenado que devolverá la información de su serie.

Código fuente del procedimiento almacenado:

CREATE PROCEDURE dbo.stpInformacoes_Serie (
    @Nome_Serie NVARCHAR(MAX)
)
AS BEGIN

    DECLARE 
        @URL NVARCHAR(MAX),
        @Retorno1 NVARCHAR(MAX),
        @Retorno2 NVARCHAR(MAX),
        @Retorno3 NVARCHAR(MAX)

    SET @URL = 'http://api.tvmaze.com/search/shows?q=' + @Nome_Serie

    -- Faz uma consulta ao webservice para consultar as informações da série
    EXEC CLR.dbo.stpWs_Requisicao 
        @Ds_Url = @URL, -- nvarchar(max)
        @Ds_Metodo = N'GET', -- nvarchar(max)
        @Ds_Parametros = N'', -- nvarchar(max)
        @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
        @Ds_Retorno_OUTPUT = @Retorno1 OUTPUT -- nvarchar(max)


    -- Remove os caracteres [] do começo e fim da string
    SET @Retorno1 = SUBSTRING(@Retorno1, 2, LEN(@Retorno1) - 2)

    -- Recupera o ID da série
    DECLARE @Id_Serie INT = CAST(JSON_VALUE(@Retorno1,'$.show.id') AS INT)
    DECLARE @Serie NVARCHAR(100) = JSON_VALUE(@Retorno1,'$.show.name')

    -- Recupera informações do episódio anterior
    SET @Url = 'http://api.tvmaze.com/shows/' + CAST(@Id_Serie AS VARCHAR(5)) + '?embed=previousepisode'

    EXEC CLR.dbo.stpWs_Requisicao 
        @Ds_Url = @URL, -- nvarchar(max)
        @Ds_Metodo = N'GET', -- nvarchar(max)
        @Ds_Parametros = N'', -- nvarchar(max)
        @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
        @Ds_Retorno_OUTPUT = @Retorno2 OUTPUT -- nvarchar(max)


    -- Recupera informações do próximo episódio
    SET @Url = 'http://api.tvmaze.com/shows/' + CAST(@Id_Serie AS VARCHAR(5)) + '?embed=nextepisode'

    EXEC CLR.dbo.stpWs_Requisicao 
        @Ds_Url = @URL, -- nvarchar(max)
        @Ds_Metodo = N'GET', -- nvarchar(max)
        @Ds_Parametros = N'', -- nvarchar(max)
        @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
        @Ds_Retorno_OUTPUT = @Retorno3 OUTPUT -- nvarchar(max)


    -- Extrai as informações dos episódios com JSON_VALUE e guarda numa tabela
    IF (OBJECT_ID('tempdb..#Episodios') IS NOT NULL) DROP TABLE #Episodios
    CREATE TABLE #Episodios (
        Ds_Serie NVARCHAR(100),
        Ds_Tipo VARCHAR(50),
        Nr_Temporada INT,
        Ds_Episodio NVARCHAR(100),
        Nr_Episodio INT,
        Dt_Episodio DATE,
        Hr_Episodio NVARCHAR(5)
    )

    INSERT INTO #Episodios
    SELECT  
        @Serie AS Ds_Serie ,
        'Anterior' AS Ds_Tipo,
        JSON_VALUE(@Retorno2, '$._embedded.previousepisode.season') AS Nr_Temporada,
        JSON_VALUE(@Retorno2, '$._embedded.previousepisode.name') AS Ds_Episodio,
        JSON_VALUE(@Retorno2, '$._embedded.previousepisode.number') AS Nr_Episodio,
        JSON_VALUE(@Retorno2, '$._embedded.previousepisode.airdate') AS Dt_Episodio,
        JSON_VALUE(@Retorno2, '$._embedded.previousepisode.airtime') AS Hr_Episodio

    UNION ALL

    SELECT  
        @Serie AS Ds_Serie,
        'Próximo' AS Ds_Tipo,
        ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.season'), '') AS Nr_Temporada,
        ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.name'), '') AS Ds_Episodio,
        ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.number'), '') AS Nr_Episodio,
        ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.airdate'), '') AS Dt_Episodio,
        ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.airtime'), '') AS Hr_Episodio


    SELECT * FROM #Episodios


END

Ejemplos de uso del Procedimiento:

Un gran consejo que nos dio Reginaldo es utilizar el sitio web. Formateador y validador JSON para mejorar la visualización del JSON devuelto por la consulta del servicio web, como se muestra en el siguiente ejemplo.

JSON original:

JSON formateado:

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

Si desea crear un monitoreo automatizado a través de un trabajo en el Agente SQL Server, que le envía un correo electrónico cuando un episodio está a punto de publicarse, asegúrese de consultar la publicación que mencioné en el blog de Reginaldo Silva, que tiene esta solución allí.

¡Abrazo!