Click on the banner to learn about and purchase my database training on Azure

SQL Server 2016 - How to Query Zip Code Information Using the Bemean API and JSON_VALUE Function

Views: 1.601 views
Reading Time: 3 minutes

Hello everybody!
All right with you ?

In this quick post, I will once again demonstrate the use of OLE Automation and CLR 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. This time, I will show you how to consult the city, state, neighborhood and street information from an informed zip code, which is widely used today.

Since the API return contains JSON format data, I will use the SQL Server 2016 JSON_VALUE function. If you want to know a lot more about handling JSON strings and files in SQL Server 2016, check out my post SQL Server 2016 - Using Native JSON Support (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).

This article is a complement to my post. How to query information from a zip code in SQL Server, where I demonstrated how to query data from a zip code using the API and that returned the data in XML format. I decided to make a new post demonstrating how to do this using another API and treating the data in JSON format.

Querying CEP Data

To perform this operation, I will use the great service of the page Bemean Postcode to see the zip code information.

To perform Web requests through SQL Server, you can use the solution using OLE Automation, which I will demonstrate below or using the Stored Procedure. stpWs_Requiring (my favorite form) from SQL CLR (C #), which I demonstrated in the post Performing POST and GET requests using CLR (C #) in SQL Server.

Querying CEP Information Using OLE Automation

Now I will demonstrate how to perform this query using OLE Automation. The routine is prepared to enable the feature on the instance if it is not enabled and deactivate again at the end of execution.

View source:

Example of use:


Querying CEP Information Using the CLR

Now I will demonstrate how to perform this query using CLR (C #), which in my view is the best solution, although it is more complex to implement and requires knowledge in a programming language (C # or VB).

If you don't know what CLR is and would like to learn more about this great feature that can be used in SQL Server, see the post. Introduction to SQL Common Language Runtime (CLR) in SQL Server. The CLR procedure source code stpWs_Requiring is available in the post Performing POST and GET requests using CLR (C #) in SQL Server.

View source:

Example of use:


That's it folks!
I hope you enjoyed this post.

Hug and see you next time.