The Challenges ahead

Basically there are 2 challenges here. First, we need to call an API using SQL Server. So that we can create a query to execute a request against that API.

Second, if that works correctly, we will get a response. In this case, an XML response. Now we need to query the XML response to get the values we need.

Let's enable SQL Server to do the API Calls

By default, SQL Server is not configured to make API calls. But the functionality is definetly there. You just have to enable it. To do that, you must be logged in as a Sys Admin and run the following query.

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'Ole Automation Procedures', 1
reconfigure
GO

With that activated, you can now make calls to any API you want.

Some preparation first

To keep this post simple I already prepared a script for you. you can download the script here: Geocode.zip

When you have the script, you will see that you will need an api key. Just search for <your_api_key>.

To use the Google Maps API, you need to create an account which you can do here. This is a pay-as-you-go API with the first $ 200 per month free. This is enough for 40.000 API calls so it should be sufficient for many use cases. By default automatic billing is turned off so the API will stop working once you reach the monthly limit. By enabling the automatic billing, the API will continue working but you will be billed.

Let's break down the script

First things first: the creation of a stored procedure. For this one I added some parameters for ease of use:

create or alter procedure spGeocode (
  @Address nvarchar(80) = null output,
  @City nvarchar(40) = null output,
  @State nvarchar(40) = null output,
  @Country nvarchar(40) = null output,
  @PostalCode nvarchar(20) = null output,
  @Municipality nvarchar(40) = null output,
  @GPSLatitude numeric(9,6) = null output,
  @GPSLongitude numeric(9,6) = null output,
  @MapURL nvarchar(1024) = null output
) as

As you can see, the different parts of the address can be added one by one. But as you will see with the examples at the bottom of the script, you can simply use just the first parameter and it will work just fine.

Reason for that is that the individual parameters are put together building the URL anyway:

set @URL = 'https://maps.google.com/maps/api/geocode/xml?sensor=false&address=' +
  case when @Address is not null then @Address else '' end +
  case when @City is not null then ', ' + @City else '' end +
  case when @State is not null then ', ' + @State else '' end +
  case when @PostalCode is not null then ', ' + @PostalCode else '' end +
  case when @Country is not null then ', ' + @Country else '' end
set @URL = REPLACE(@URL, ' ', '+')
set @URL = @URL + '&key=<your_api_key>'

Do note that the last line is where you need to insert your Google Maps API Key.

Let's make an API Call

SQL Server itself cannot make API Calls directly. But it can instantiate an object that is able to do the hard work. There are several System Stored Procedures involved which are documented here. So the next step is to instantiate the correct object:

exec @Result = sp_OACreate 'MSXML2.ServerXMLHttp', @Object out

Having done that, you can now use that object and make the API Call:

begin try
  exec @Result = sp_OAMethod @Object, 'open', null, 'GET', @URL, false
  exec @Result = sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'
  exec @Result = sp_OAMethod @Object, send, null, ''
  exec @Result = sp_OAGetProperty @Object, 'status', @HTTPStatus out
  exec @Result = sp_OAGetProperty @Object, 'responseXML.xml', @Response out
end try
begin catch
  set @ErrorMsg = error_message()
end catch

exec @Result = sp_OADestroy @Object

As you can see: once you are done making the API Call, the instantiated object can be destroyed. Just to keep things clean and running smoothly.

If there was an error, the ErrorMessage will be filled and the script will then raise that error.

If no error occured, The Response will be filled as a string of text. So that must be casted to XML using the XML Data Type:

declare @XML xml
set @XML = cast(@Response as XML)
select @XML

Selecting the @XML Variable will show you the XML as returned by the API. Just click on it in the results to view its content.

Get the Values from the XML

To get a Value from the XML, you need to specify the path inside the XML. So if you want to get the City, you need to go for this one:

set @City = @XML.value('(/GeocodeResponse/result/address_component[type="locality"]/long_name) [1]', 'varchar(40)')

So from the Root of the XML you go to the GeocodeResponse. From there to the result and finally the address_component. Since there are multiple address components, you also need to query the coorect type by using [type="locality"].

Once you are in the correct address_component XML Node, you can get the value that is inside the long_name Node.

Now if you want to have a Google Maps URL for use in your application, you can construct that URL using the longitude and latitude:

set @MapURL = 'http://maps.google.com/maps?f=q&hl=en&q=' +
cast(@GPSLatitude as nvarchar(20)) + '+' +
cast(@GPSLongitude as nvarchar(20))

And finally, by selecting all the variables, you now have a result set which you can use in any way you want:

select
  @GPSLatitude as GPSLatitude,
  @GPSLongitude as GPSLongitude,
  @City as City,
  @State as State,
  @PostalCode as PostalCode,
  @Address as Address,
  @Municipality as Municipality,
  @MapURL as MapURL,
  @XML as XMLResults

That's it for today!

As you can see, by using the Ole Automation Procedures you can make calls to an API. By casting the Response as an XML Document, you can then query the XML Document by specifying the path.

So yes, this proves that you can use API's in your queries and use the results any way you want.

Well, that was the Query for this week. Let me know how you will use this one in your Projects And as always: May the Source be with you!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!