Is it possible to consume data from a webservice within a Sqlserver directly?

Question

I have to migrate from one Mysql database to another in Sqlserver. In this migration, I have the following problem: not all address records are complete but I have the zip code of most of them. My idea then is:

• Using the website service http://viacep.com.br to complete my data.

I’ve thought of many ways to do this, I think the best way is through a cursor but, I don’t know how to capture this data directly from within Sqlserver. It is possible to consume this data directly without the need to create a small API or something?

Answer 1

I found the solution at that link. Sqlserver (mine in version 2016) has the feature to consume data from a webservice and based on it to capture the data. The end result of my job was this way:

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.fnViaCep') AND xtype IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[fnViaCep];
GO

CREATE FUNCTION dbo.fnViaCep(@CEP varchar(10)) 
RETURNS @T TABLE (
    cep varchar(10),
    logradouro varchar(100),
    complemento varchar(100),
    bairro varchar(100),
    localidade varchar(100),
    unidade varchar(100),
    uf char(2),
    ibge char(7),
    gia varchar(10)
) AS BEGIN

    DECLARE @authHeader VARCHAR(64),
            @contentType VARCHAR(64),
            @postData VARCHAR(2000),
            @responseText VARCHAR(2000),
            @responseXML VARCHAR(2000),
            @ret INT,
            @status VARCHAR(32),
            @statusText VARCHAR(32),
            @token INT,
            @url VARCHAR(256),
            @xml XML;

    SET @authHeader = 'BASIC 0123456789ABCDEF0123456789ABCDEF';
    SET @contentType = 'application/x-www-form-urlencoded';
    SET @url = 'http://viacep.com.br/ws/' + @CEP + '/xml/';

    -- Open the connection.
    EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
    -- IF @ret <> 0 PRINT (CAST('Unable to open HTTP connection.' AS INT));

    -- Send the request.
    EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
    EXEC @ret = sp_OAMethod @token, 'send', NULL

    -- Handle the response.
    EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
    EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
    EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

    SET @xml = CONVERT(XML, replace(@responseText,'<?xml version="1.0" encoding="UTF-8"?>',''), 1);

    INSERT INTO @T
    SELECT
        t.c.value('cep[1]', 'varchar(10)') as cep,  
        t.c.value('logradouro[1]', 'varchar(100)') as logradouro,  
        t.c.value('complemento[1]', 'varchar(100)') as complemento,
        t.c.value('bairro[1]', 'varchar(100)') as bairro,
        t.c.value('localidade[1]', 'varchar(100)') as localidade,
        t.c.value('unidade[1]', 'varchar(100)') as unidade,
        t.c.value('uf[1]', 'char(2)') as uf,
        t.c.value('ibge[1]', 'char(7)') as ibge
        t.c.value('gia[1]', 'varchar(10)') as gia,
    FROM @xml.nodes('//xmlcep') t(c)  

    -- Close the connection.
    EXEC @ret = sp_OADestroy @token;
    -- IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);

RETURN
END
GO

With this you have numerous possibilities of data validation and the possibility to complete information in databases as you want. The form of use is simple:

// seleção simples de um endereço usando o CEP
select * from fnViaCep('31520230')