Hello people,
Good day.
In this quick post, I will once again demonstrate the use of OLE Automation procedures to consume information on the web and bring it to our SQL Server database so that we can work with this information as needed. If you want to use the Bemean API, which returns data in JSON format, see more in the post. SQL Server 2016 - How to Query Zip Code Information Using the Bemean API and JSON_VALUE Function
This time, I'll show you how to query city, state, neighborhood, add-on, and IBGE code information from an informed zip code.
Querying CEP Data
To perform this operation, I will use the great service of the page viacep.com.br to see the zip code information.
I will filter the input zip code so that it contains only numbers. After that, we make a GET request on the viacep URL and then handle the return via XQuery.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
CREATE PROCEDURE dbo.stpConsulta_CEP ( @Nr_CEP VARCHAR(20) ) AS BEGIN DECLARE @obj INT, @Url VARCHAR(255), @resposta VARCHAR(8000), @xml XML -- Recupera apenas os números do CEP DECLARE @startingIndex INT = 0 WHILE (1=1) BEGIN SET @startingIndex = PATINDEX('%[^0-9]%', @Nr_CEP) IF (@startingIndex <> 0) SET @Nr_CEP = REPLACE(@Nr_CEP, SUBSTRING(@Nr_CEP, @startingIndex, 1), '') ELSE BREAK END SET @Url = 'http://viacep.com.br/ws/' + @Nr_CEP + '/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('(/xmlcep/cep)[1]', 'varchar(9)') AS CEP, @xml.value('(/xmlcep/logradouro)[1]', 'varchar(200)') AS Logradouro, @xml.value('(/xmlcep/complemento)[1]', 'varchar(200)') AS Complemento, @xml.value('(/xmlcep/bairro)[1]', 'varchar(200)') AS Bairro, @xml.value('(/xmlcep/localidade)[1]', 'varchar(200)') AS Cidade, @xml.value('(/xmlcep/uf)[1]', 'varchar(200)') AS UF, @xml.value('(/xmlcep/ibge)[1]', 'varchar(200)') AS IBGE END |
After we create the function, we will use it to test the result:
That's it!
Thank you and see you next time!
Hello Dirceu, how are you?
I replicated this procedure in my environment and the result is returning empty for any query I make. I know that the post is a little old, but the API continues to work (on the web, queries return address data correctly). Can you validate that for you the procedure still works or also returns NULL as an answer? So I can be sure that the problem is only happening to me.
Hugs.
Is there any performance impact if I want to consume an API, if I use my API to validate address data by zip code, does the bank behave well? Can I work with excessive consumption to validate something via API?
Thankful!
Paul,
Thanks for the visit!
It has performance impact, yes. Each API query consumes an average of about 480ms. Additionally, Google, for example, has an IP limitation of 1.000 API queries per day. After that, he himself blocks his IP for a certain period of time. For unlimited queries, you must purchase a paid version of the Google API.
I believe that viacep.com.br should have such a limit, as well as most companies that offer this type of service.
A more performative and perhaps inexpensive alternative is to purchase an updated zip code base directly from the Post Office. Instead of making queries via the net, you could simply make a simple select on this purchased post office base.
Dirceu Resende,
Thanks for the information, and I already did this process with the post office base, but it is outdated there are new neighborhoods that in new registrations does not fill the fields because it does not find, I need to update the data in the base, today I look for the zip code in the base if I can't find I search the Post Office API and I update my database but there are data that when you issue notes you have an outdated or incomplete address, so is it better to buy buy an update, and how about the google paid API how does it go? where there is information, I thought I only had free.
Congratulations for the excellent post. Much obliged
Igo, tested here and for me is working normally. Which zip code did you use that is giving error?
webservice is not returning values, it has HTTP 502 error