Consumiendo la API de Google Maps usando OLE Automation en SQL Server

Hola, chicos,
¡Buenas noches!

En esta publicación, le daré un consejo rápido sobre cómo consumir la API de Google Maps usando OLE Automation en SQL Server para recuperar más información sobre una ciudad solo por su nombre.

¿No sabes cómo habilitar la automatización OLE en tu instancia? Ver más en la publicación Habilitación de la automatización OLE a través de T-SQL en SQL Server.

Implementación del Procedimiento Almacenado:

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

Usando el procedimiento almacenado creado:

XML devuelto por llamada 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>

¡Eso es todo, amigos!
Hasta el próximo post.