Querying SQL Server object tracking

Views: 2.307 views

In this article

Reading Time: 3 minutes

Hello people,
Good afternoon!

In this post today I will bring something very cool, which is a Transact-SQL stored procedure where you can query and track order information and objects using data from the Post Office site in SQL Server. For this, I will use the MSXML2.ServerXMLHTTP object and OLE Automation procedures.

This is very useful for e-commerce companies that can use this SP to integrate Post Office tracking with their systems and display on-screen delivery of their products.

UPDATE: From day 10 / 06 / 2017 this feature stopped working, since Correios decided to disable the WEBSRO service, which everyone used to quickly consult the tracking of objects. However, I made the post SQL Server - How to Track Orders and Mail Objects (After WEBSRO Deactivated) that circumvents this problem. 🙂

Before we get started, let's look at some prerequisites for using this procedure.:
- Enable the OLE Automation feature on your SQL Server (see how this post)
- fncRemove_HTML function to remove HTML tags from a varchar string (see more this post)
- fncSplit_Texto function to break a string into substrings using a delimiter (see more this post)

An important detail I noticed in the development of this SP is that if the amount of XML characters returned by the sp_OAGetProperty procedure is greater than 4000 characters, the SP return will be NULL, since the sp_OAGetProperty procedure has this limitation of 4000 characters. To work around this problem, instead of using an OUTPUT variable to capture the data, I insert it into a temporary table and then work with the data. 🙂

SP usage examples

Implementing the Stored Procedure in Your Database

View source

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

That's it!
Did you like this procedure? Now you can create tracking for your orders .. kkkk

sql server tsql transact sql query tracking parcel post objects

Rogerio said:

9 January 2018 to 09: 56

I did the step by step and it did not work could you help me?

- Dirceu Resende said:
  
  9 January 2018 to 13: 35
  
  Rogério, good afternoon. Did you give a mistake? What happened?
  
thiago said:

29 from 2016 to 22 at 10: XNUMX

Excellent.

roger said:

19 January 2016 to 19: 50

Incredible guy. Excellent post and very enlightening! Have you thought about the possibility of recording some videos for YouTube? if you already have a channel, tell me the name ... Hug!

Other Languages

Subscribe to a blog by email

Blog Views

Categories

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Recent Posts

Other Languages

Are you looking for something?

Subscribe to Blog by Email

Total Views

Courses and training

Microsoft MVP

Acknowledgments and Certificates

Categories

Archives

Recent Posts

Querying SQL Server object tracking

SP usage examples

Implementing the Stored Procedure in Your Database

sql server tsql transact sql query tracking parcel post objects

You may also like ...

Comments

Leave a Comment Cancel reply

SP usage examples

Implementing the Stored Procedure in Your Database

sql server tsql transact sql query tracking parcel post objects

You may also like ...

SQL Server – How to write a full number in Portuguese and English using T-SQL function

SQL Server Express and Developer - How far can we use free versions of SQL Server?

SQL Server - How to calculate the difference in years (age) between two dates using T-SQL or CLR

Comments

Leave a Comment Cancel reply