Consultando o rastreamento de objetos dos Correios pelo SQL Server

Post Views 2,423 views

Neste artigo

Reading time 3 minutes

Olá pessoal,
Boa tarde!

Neste post de hoje vou trazer algo muito legal, que é uma stored procedure Transact-SQL onde é possível consultar e rastrear as informações de encomendas e objetos utilizando dados do próprio site dos Correios no SQL Server. Para isso, vou utilizar o objeto MSXML2.ServerXMLHTTP e procedures OLE Automation.

Isso é bem útil para empresas de e-Commerce que podem utilizar essa SP para integrar o rastreamento dos Correios a seus sistemas e exibir na tela o andamento da entrega de seus produtos.

UPDATE: A partir do dia 10/06/2017 esse recurso parou de funcionar, pois o Correios decidiu desativar o serviço do WEBSRO, que todo mundo utilizava para consultar de forma rápida, o rastreamento dos objetos. Entretanto, fiz o post SQL Server – Como rastrear encomendas e objetos dos correios (após desativação do WEBSRO) que contorna esse problema. 🙂

Antes de iniciarmos, vejamos alguns pré-requisitos para utilizar essa procedure:
– Habilitar o recurso OLE Automation no seu servidor SQL Server (veja como neste post)
– Função fncRemove_HTML para remover tags HTML de uma string varchar (veja mais neste post)
– Função fncSplit_Texto para quebrar uma string em substrings utilizando um delimitador (veja mais neste post)

Um detalhe importante que observei no desenvolvimento dessa SP, é que se a quantidade de caracteres do XML de retornado pela procedure sp_OAGetProperty for superior a 4000 caracteres, o retorno da SP será NULL, uma vez que a procedure sp_OAGetProperty possui essa limitação de 4000 caracteres. Para contornar esse problema, ao invés de usar uma variável de OUTPUT para capturar os dados, eu os insiro em uma tabela temporária e depois trabalho com os dados. 🙂

Exemplos de uso da SP

Implementando a Stored Procedure no seu banco de dados

Visualizar código-fonte

CREATE PROCEDURE [dbo].[stpBusca_Rastreamento_Correios] (
    @Ds_Rastreamento VARCHAR(13)
)
AS BEGIN
 

    SET NOCOUNT ON

        
    ------------------------------------------------------------------------
    -- RECUPERAÇÃO DAS INFORMAÇÕES
    ------------------------------------------------------------------------

    DECLARE 
        @obj INT,
        @Url VARCHAR(8000),
        @xml VARCHAR(MAX),
        @resposta VARCHAR(MAX)
        

    IF (OBJECT_ID('tempdb..#xml') IS NOT NULL) DROP TABLE #xml
    CREATE TABLE #xml ( 
        Ds_Dados VARCHAR(MAX)
    )
    

    SET @Url = 'http://websro.correios.com.br/sro_bin/txect01$.QueryList?P_LINGUA=001&P_TIPO=001&P_COD_UNI=' + @Ds_Rastreamento

    
    EXEC sys.sp_OACreate @progid = 'MSXML2.ServerXMLHTTP', @objecttoken = @obj OUT, @context = 1
    EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, false
    EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
    EXEC sys.sp_OAMethod @obj, 'send'


    INSERT INTO #xml(Ds_Dados)
    EXEC sys.sp_OAGetProperty @obj, 'responseText' --, @resposta OUT
    
    
    EXEC sys.sp_OADestroy @obj


    SELECT TOP 1 @resposta = Ds_Dados FROM #xml

    
    SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS
    

    ------------------------------------------------------------------------
    -- TRATAMENTO DOS DADOS
    ------------------------------------------------------------------------

    SET @xml = REPLACE(@xml, '</td><td>', ' | ')
    

    IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados
    SELECT
        ROW_NUMBER() OVER(ORDER BY Id) AS Linha,
        LTRIM(RTRIM(REPLACE(Palavra, CHAR(13), ''))) AS Palavra
    INTO
        #Dados
    FROM
        dbo.fncSplitTexto(dbo.fncRemove_HTML(@xml), CHAR(10))
    WHERE
        NULLIF(REPLACE(Palavra, CHAR(13), ''), '') IS NOT NULL
    
    SELECT * FROM #Dados

    ------------------------------------------------------------------------
    -- RESULTADO FINAL
    ------------------------------------------------------------------------

    DECLARE @Qt_Linhas INT = (SELECT COUNT(*) FROM #Dados)

    IF ((SELECT COUNT(*) FROM #Dados WHERE Palavra LIKE '%nao possui dados sobre o objeto%') > 0)
    BEGIN
        
        SELECT Palavra AS Rastreamento
        FROM #Dados
        WHERE Palavra LIKE '%nao possui dados sobre o objeto%'

    END
    ELSE BEGIN

        SELECT Palavra AS Rastreamento
        FROM #Dados
        WHERE Linha > 7
        AND Linha < @Qt_Linhas

    END
    
END

CREATE PROCEDURE [dbo].[stpBusca_Rastreamento_Correios] (

@Ds_Rastreamento VARCHAR(13)

)

AS BEGIN

SET NOCOUNT ON

------------------------------------------------------------------------

-- RECUPERAÇÃO DAS INFORMAÇÕES

------------------------------------------------------------------------

DECLARE

@obj INT,

@Url VARCHAR(8000),

@xml VARCHAR(MAX),

@resposta VARCHAR(MAX)

IF (OBJECT_ID('tempdb..#xml') IS NOT NULL) DROP TABLE #xml

CREATE TABLE #xml (

Ds_Dados VARCHAR(MAX)

)

SET @Url = 'http://websro.correios.com.br/sro_bin/txect01$.QueryList?P_LINGUA=001&P_TIPO=001&P_COD_UNI=' + @Ds_Rastreamento

EXEC sys.sp_OACreate @progid = 'MSXML2.ServerXMLHTTP', @objecttoken = @obj OUT, @context = 1

EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, false

EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'

EXEC sys.sp_OAMethod @obj, 'send'

INSERT INTO #xml(Ds_Dados)

EXEC sys.sp_OAGetProperty @obj, 'responseText' --, @resposta OUT

EXEC sys.sp_OADestroy @obj

SELECT TOP 1 @resposta = Ds_Dados FROM #xml

SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS

------------------------------------------------------------------------

-- TRATAMENTO DOS DADOS

------------------------------------------------------------------------

SET @xml = REPLACE(@xml, '</td><td>', ' | ')

IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados

SELECT

ROW_NUMBER() OVER(ORDER BY Id) AS Linha,

LTRIM(RTRIM(REPLACE(Palavra, CHAR(13), ''))) AS Palavra

INTO

#Dados

FROM

dbo.fncSplitTexto(dbo.fncRemove_HTML(@xml), CHAR(10))

WHERE

NULLIF(REPLACE(Palavra, CHAR(13), ''), '') IS NOT NULL

SELECT * FROM #Dados

------------------------------------------------------------------------

-- RESULTADO FINAL

------------------------------------------------------------------------

DECLARE @Qt_Linhas INT = (SELECT COUNT(*) FROM #Dados)

IF ((SELECT COUNT(*) FROM #Dados WHERE Palavra LIKE '%nao possui dados sobre o objeto%') > 0)

BEGIN

SELECT Palavra AS Rastreamento

FROM #Dados

WHERE Palavra LIKE '%nao possui dados sobre o objeto%'

END

ELSE BEGIN

SELECT Palavra AS Rastreamento

FROM #Dados

WHERE Linha > 7

AND Linha < @Qt_Linhas

END

É isso aí!
Gostaram dessa procedure? Agora dá pra criar monitoramentos para suas encomendas.. kkkk

sql server tsql transact sql query rastreamento encomendas objetos correios

Rogerio disse:

January 9, 2018 às 09:56

fiz o passo a passo e não funcionou você consegui me ajudar?

Responder
- Dirceu Resende disse:
  
  January 9, 2018 às 13:35
  
  Rogério, boa tarde. Deu algum erro pra você? O que aconteceu?
  
  Responder
thiago disse:

August 29, 2016 às 22:10

Excelente.

Responder
roger disse:

January 19, 2016 às 19:50

Incrivel cara. Excelente post e muito esclarecedor! Ja pensou na possibilidade de gravar alguns videos para o YouTube ? caso ja possua um canal, me informe o nome… Abraço!

Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

Consultando o rastreamento de objetos dos Correios pelo SQL Server

Exemplos de uso da SP

Implementando a Stored Procedure no seu banco de dados

sql server tsql transact sql query rastreamento encomendas objetos correios

You may also like...

4 Responses

Deixe uma respostaCancelar resposta

Exemplos de uso da SP

Implementando a Stored Procedure no seu banco de dados

sql server tsql transact sql query rastreamento encomendas objetos correios

You may also like...

SQL Server – Como criptografar e descriptografar senhas (com Salt) utilizando o CLR (C#)

SQL Server 2016 – Como proteger seus dados utilizando o Row Level Security (RLS)

SQL Server – Como importar arquivos de texto para o banco (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)

4 Responses

Deixe uma respostaCancelar resposta