How to Calculate Shipping Cost and Delivery Time Using Correios WebService in SQL Server

Hey guys!
Good afternoon!

In this post, I will once again demonstrate the use of OLE Automation in SQL Server to consume data from a web service. This time, I will consume the Correios service to calculate shipping times and costs (widely used for e-Commerce).

Getting to know the Correios WebService

Before we start creating the Stored Procedure that will query the data, we first need to understand the parameters and flags of the Correios service.

Correios WebService - Input Parameters

Correios WebService - Return Parameters

Correios WebService - Service Codes

Creating the Stored Procedure to query the data

With the code snippet below, we will create the Stored Procedure that we will use to query shipping value and delivery time data using procedures OLE Automation to perform the GET request and return the Web XML, which we will read using XQuery.

CREATE PROCEDURE dbo.stpConsulta_Frete_Correios (
    @sCepOrigem VARCHAR(12),
    @sCepDestino VARCHAR(12),
    @nCdServico INT,
    @nVlPeso FLOAT = 0.1,
    @nCdFormato SMALLINT = 1,
    @nVlComprimento INT = 20,
    @nVlAltura INT = 5,
    @nVlLargura INT = 15,
    @nVlDiametro INT = 0,
    @CdMaoPropria CHAR(1) = 'n',
    @nVlValorDeclarado FLOAT = 0,
    @CdAvisoRecebimento CHAR(1) = 'n'
)
AS BEGIN
 
 
    DECLARE 
        @obj INT,
        @Url VARCHAR(500),
        @resposta VARCHAR(8000),
        @xml XML
        
        
        
    -- Recupera apenas os números do CEP de Origem
    DECLARE @startingIndex INT = 0
    
    WHILE (1=1)
    BEGIN
      
        SET @startingIndex = PATINDEX('%[^0-9]%', @sCepOrigem)  
        
        IF (@startingIndex <> 0)
            SET @sCepOrigem = REPLACE(@sCepOrigem, SUBSTRING(@sCepOrigem, @startingIndex, 1), '')  
        ELSE    
            BREAK
            
    END
    
    
    
    -- Recupera apenas os números do CEP de Destino
    SET @startingIndex = 0
    
    WHILE (1=1)
    BEGIN
      
        SET @startingIndex = PATINDEX('%[^0-9]%', @sCepDestino)  
        
        IF (@startingIndex <> 0)
            SET @sCepDestino = REPLACE(@sCepDestino, SUBSTRING(@sCepDestino, @startingIndex, 1), '')  
        ELSE    
            BREAK
            
    END
        
 
 
    SET @Url = 'http://ws.correios.com.br/calculador/CalcPrecoPrazo.aspx?' + 
    'sCepOrigem=' + @sCepOrigem + 
    '&sCepDestino=' + @sCepDestino + 
    '&nVlPeso=' + CAST(@nVlPeso AS VARCHAR(20)) + 
    '&nCdFormato=' + CAST(@nCdFormato AS VARCHAR(20)) + 
    '&nVlComprimento=' + CAST(@nVlComprimento AS VARCHAR(20)) + 
    '&nVlAltura=' + CAST(@nVlAltura AS VARCHAR(20)) + 
    '&nVlLargura=' + CAST(@nVlLargura AS VARCHAR(20)) + 
    '&sCdMaoPropria=' + @CdMaoPropria + 
    '&nVlValorDeclarado=' + CAST(@nVlValorDeclarado AS VARCHAR(20)) + 
    '&sCdAvisoRecebimento=' + @CdAvisoRecebimento + 
    '&nCdServico=' + CAST(@nCdServico AS VARCHAR(20)) + 
    '&nVlDiametro=' + CAST(@nVlDiametro AS VARCHAR(20)) + '&StrRetorno=xml'
 
    EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT
    EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, FALSE
    EXEC sys.sp_OAMethod @obj, 'send'
    EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT
    EXEC sys.sp_OADestroy @obj
    
    SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS
    
    
    SELECT
        @xml.value('(/Servicos/cServico/Codigo)[1]', 'bigint') AS Codigo_Servico,
        @xml.value('(/Servicos/cServico/Valor)[1]', 'varchar(20)') AS Valor_Frete,
        @xml.value('(/Servicos/cServico/PrazoEntrega)[1]', 'int') AS Prazo_Entrega,
        @xml.value('(/Servicos/cServico/ValorSemAdicionais)[1]', 'varchar(20)') AS Valor_Sem_Adicionais,
        @xml.value('(/Servicos/cServico/ValorMaoPropria)[1]', 'varchar(20)') AS Valor_Entrega_Em_Maos,
        @xml.value('(/Servicos/cServico/ValorAvisoRecebimento)[1]', 'varchar(20)') AS Valor_Aviso_Recebimento,
        @xml.value('(/Servicos/cServico/EntregaDomiciliar)[1]', 'varchar(20)') AS Entrega_Domiciliar,
        @xml.value('(/Servicos/cServico/EntregaDomiciliar)[1]', 'varchar(1)') AS Entrega_Domiciliar,
        @xml.value('(/Servicos/cServico/EntregaSabado)[1]', 'varchar(1)') AS Entrega_Sabado,
        @xml.value('(/Servicos/cServico/Erro)[1]', 'int') AS Codigo_Erro,
        @xml.value('(/Servicos/cServico/MsgErro)[1]', 'varchar(500)') AS Mensagem_Erro
        
 
END

Usage examples

Carrying out a simple test, filling in only the mandatory parameters

Carrying out a more complete test, filling in all SP parameters

Calling SP, but reporting an invalid ZIP code

That's it, folks!
Until the next post!