Consultando o rastreamento de objetos dos Correios pelo SQL Server

Visualizações: 2.420 views

Neste artigo

Tempo de Leitura: 3 minutos

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:

9 de janeiro de 2018 às 09:56

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

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

29 de agosto de 2016 às 22:10

Excelente.

Responder
roger disse:

19 de janeiro de 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

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

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...

Como converter números para algarismos romanos no SQL Server

Procurando uma string em um job (ou step) no SQL Server

SQL Server – Como exportar dados do banco para arquivo texto (CLR, OLE, BCP)

4 Responses

Deixe uma respostaCancelar resposta