Consumindo a API do Google Maps usando OLE Automation no SQL Server

Olá pessoal,
Boa noite!

Nesse post vou dar uma dica bem rapidinha de como consumir a API do Google Maps usando OLE Automation no SQL Server para recuperar mais informações de uma cidade apenas pelo seu nome.

Não sabe como ativar OLE Automation na sua instância? Veja mais no post Habilitando OLE Automation via T-SQL no SQL Server.

Implementação da Stored Procedure:

CREATE PROCEDURE dbo.stpBusca_Informacoes_Cidade(
    @Ds_Cidade VARCHAR(100)
)
AS BEGIN

    -- DECLARE @Ds_Cidade VARCHAR(100) = 'Vitória'

    DECLARE 
        @obj INT,
        @Url VARCHAR(255),
        @resposta VARCHAR(8000),
        @xml XML
    
    SET @Url = 'http://maps.googleapis.com/maps/api/geocode/xml?address=' + @Ds_Cidade + '&sensor=false'
    
    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
    
    SELECT
        @xml.value('(/GeocodeResponse/result/address_component/long_name)[1]', 'varchar(200)') AS Cidade,
        @xml.value('(/GeocodeResponse/result/formatted_address)[1]', 'varchar(200)') AS Cidade_Completo,
        @xml.value('(/GeocodeResponse/result/address_component/long_name)[3]', 'varchar(200)') AS Estado,
        @xml.value('(/GeocodeResponse/result/address_component/short_name)[3]', 'varchar(200)') AS Estado_Sigla,
        @xml.value('(/GeocodeResponse/result/address_component/long_name)[4]', 'varchar(200)') AS Pais,
        @xml.value('(/GeocodeResponse/result/address_component/short_name)[4]', 'varchar(200)') AS Pais_Sigla,
        @xml.value('(/GeocodeResponse/result/geometry/location/lat)[1]', 'varchar(200)') AS Latitude,
        @xml.value('(/GeocodeResponse/result/geometry/location/lng)[1]', 'varchar(200)') AS Longitude
                
END

Utilizando a Stored Procedure Criada:

XML Retornado pela Chamada da API:

<GeocodeResponse>
  <status>OK</status>
  <result>
    <type>locality</type>
    <type>political</type>
    <formatted_address>Vitoria - State of Espirito Santo, Brazil</formatted_address>
    <address_component>
      <long_name>Vitoria</long_name>
      <short_name>Vitoria</short_name>
      <type>locality</type>
      <type>political</type>
    </address_component>
    <address_component>
      <long_name>Vitoria</long_name>
      <short_name>Vitoria</short_name>
      <type>administrative_area_level_2</type>
      <type>political</type>
    </address_component>
    <address_component>
      <long_name>State of Espirito Santo</long_name>
      <short_name>ES</short_name>
      <type>administrative_area_level_1</type>
      <type>political</type>
    </address_component>
    <address_component>
      <long_name>Brazil</long_name>
      <short_name>BR</short_name>
      <type>country</type>
      <type>political</type>
    </address_component>
    <geometry>
      <location>
        <lat>-20.2976178</lat>
        <lng>-40.2957768</lng>
      </location>
      <location_type>APPROXIMATE</location_type>
      <viewport>
        <southwest>
          <lat>-20.3245888</lat>
          <lng>-40.3590796</lng>
        </southwest>
        <northeast>
          <lat>-20.2397943</lat>
          <lng>-40.2134144</lng>
        </northeast>
      </viewport>
      <bounds>
        <southwest>
          <lat>-20.3245888</lat>
          <lng>-40.3590796</lng>
        </southwest>
        <northeast>
          <lat>-20.2397943</lat>
          <lng>-40.2134144</lng>
        </northeast>
      </bounds>
    </geometry>
    <place_id>ChIJ6U43hV09uAARCdgOp35eWZc</place_id>
  </result>
</GeocodeResponse>

É isso aí, pessoal!
Até o próximo post.