Query Prediction - Fetch

Retrieve Google Places query predictions using SQL Server

Prerequisites:

See SQLHTTP easy setup for Google Places
See API Call documentation for parameter values and other information
Create the stored procedure documented below

Stored Procedure:


CREATE PROCEDURE usp_Google_Places_Query_Autocomplete_Fetch(
		@Profile varchar(100),
		@input nvarchar(4000),
		@offset int = NULL,
		@location nvarchar(100) = NULL,
		@radius int = NULL,
		@language nvarchar(5) = NULL,
		@Response nvarchar(MAX) OUTPUT,
		@StatusCode int OUTPUT,
		@StatusDescription nvarchar(MAX) OUTPUT)
AS

DECLARE @URL nvarchar(MAX)
DECLARE @QueryString varchar(MAX)
DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://maps.googleapis.com/maps/api/place/queryautocomplete/xml'

EXEC SQLHTTP.net.QueryStringBuilder   @QueryString OUTPUT,
				      @Profile,
				      'input', @input,
				      'offset', @offset,
				      'location', @location,
				      'radius', @radius,
				      'language', @language,
				      'key', '#APIKey'

SET @URL = @URL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC SQLHTTP.net.HTTPRequest	@HttpSessionID, 
				@URL = @URL,
				@Method = 'GET',
				@StatusCode = @StatusCode OUTPUT,
				@StatusDescription = @StatusDescription OUTPUT,
				@Response = @Response OUTPUT
GO

CREATE PROCEDURE usp_Google_Places_Query_Autocomplete_Fetch(

@Profile varchar(100),

@input nvarchar(4000),

@offset int = NULL,

@location nvarchar(100) = NULL,

@radius int = NULL,

@language nvarchar(5) = NULL,

@Response nvarchar(MAX) OUTPUT,

@StatusCode int OUTPUT,

@StatusDescription nvarchar(MAX) OUTPUT)

DECLARE @URL nvarchar(MAX)

DECLARE @QueryString varchar(MAX)

DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://maps.googleapis.com/maps/api/place/queryautocomplete/xml'

EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT,

@Profile,

'input', @input,

'offset', @offset,

'location', @location,

'radius', @radius,

'language', @language,

'key', '#APIKey'

SET @URL = @URL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @URL,

@Method = 'GET',

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT



Sample Usage:


DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Google_Places_Query_Autocomplete_Fetch
		@Profile = 'My Google Places',
		@input = 'Pizza near Par',
		@language = 'fr',
		@Response = @Response OUTPUT,
		@StatusCode = @StatusCode OUTPUT, 
		@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400
	EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
ELSE
	IF SQLHTTP.net.IsXmlValid(@Response) = 0
		BEGIN
			RAISERROR(@Response, 16, 1)
			RETURN
		END
	ELSE
		BEGIN
			DECLARE @X xml
			SET @X = CONVERT(xml, CONVERT(varchar(MAX), @Response))

			--The XPath syntax below was easily generated by executing the following commands:
			--EXEC SQLHTTP.net.XQueryHelper @X
			--and then executing: 
			--EXEC SQLHTTP.net.XQueryHelper @X, 'AutocompletionResponse' --
			--EXEC SQLHTTP.net.XQueryHelper @X, 'AutocompletionResponse/prediction' --

			DECLARE @status nvarchar(MAX)

			SELECT  @status = T.C.value(N'status[1]', N'nvarchar(MAX)')
			FROM @X.nodes(N'/AutocompletionResponse') T(C)

			--Continue calling the above stored procedure with the @pagetoken parameter
			--to retrieve additional records (when not NULL)

			IF @status <> 'OK'
				BEGIN
					RAISERROR('@Status', 16, 1)
					RETURN
				END
			
			SELECT  T.C.value(N'description[1]', N'nvarchar(MAX)') AS [description]
			       ,T.C.value(N'term[1]/value[1]', N'nvarchar(MAX)') AS [value1]
			       ,T.C.value(N'term[1]/offset[1]', N'nvarchar(MAX)') AS [offset1]
			       ,T.C.value(N'term[2]/value[1]', N'nvarchar(MAX)') AS [value2]
			       ,T.C.value(N'term[2]/offset[1]', N'nvarchar(MAX)') AS [offset2]
			       ,T.C.value(N'term[3]/value[1]', N'nvarchar(MAX)') AS [value3]
			       ,T.C.value(N'term[3]/offset[1]', N'nvarchar(MAX)') AS [offset3]
			FROM @X.nodes(N'/AutocompletionResponse/prediction') T(C)
		END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Google_Places_Query_Autocomplete_Fetch

@Profile = 'My Google Places',

@input = 'Pizza near Par',

@language = 'fr',

@Response = @Response OUTPUT,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

IF SQLHTTP.net.IsXmlValid(@Response) = 0

BEGIN

RAISERROR(@Response, 16, 1)

RETURN

END

ELSE

BEGIN

DECLARE @X xml

SET @X = CONVERT(xml, CONVERT(varchar(MAX), @Response))

--The XPath syntax below was easily generated by executing the following commands:

--EXEC SQLHTTP.net.XQueryHelper @X

--and then executing:

--EXEC SQLHTTP.net.XQueryHelper @X, 'AutocompletionResponse' --

--EXEC SQLHTTP.net.XQueryHelper @X, 'AutocompletionResponse/prediction' --

DECLARE @status nvarchar(MAX)

SELECT @status = T.C.value(N'status[1]', N'nvarchar(MAX)')

FROM @X.nodes(N'/AutocompletionResponse') T(C)

--Continue calling the above stored procedure with the @pagetoken parameter

--to retrieve additional records (when not NULL)

IF @status <> 'OK'

BEGIN

RAISERROR('@Status', 16, 1)

RETURN

END

SELECT T.C.value(N'description[1]', N'nvarchar(MAX)') AS [description]

,T.C.value(N'term[1]/value[1]', N'nvarchar(MAX)') AS [value1]

,T.C.value(N'term[1]/offset[1]', N'nvarchar(MAX)') AS [offset1]

,T.C.value(N'term[2]/value[1]', N'nvarchar(MAX)') AS [value2]

,T.C.value(N'term[2]/offset[1]', N'nvarchar(MAX)') AS [offset2]

,T.C.value(N'term[3]/value[1]', N'nvarchar(MAX)') AS [value3]

,T.C.value(N'term[3]/offset[1]', N'nvarchar(MAX)') AS [offset3]

FROM @X.nodes(N'/AutocompletionResponse/prediction') T(C)

END

description value1 offset1 value2 offset2 value3 offset3

-------------------------------- ------- -------- --------------- -------- ---------- --------

pizza à proximité de Paris pizza 0 à proximité de 6 Paris 21

pizza à proximité de Parramatta pizza 0 à proximité de 6 Parramatta 21

pizza à proximité de Paramus pizza 0 à proximité de 6 Paramus 21

pizza à proximité de Park City pizza 0 à proximité de 6 Park City 21

pizza à proximité de Parañaque pizza 0 à proximité de 6 Parañaque 21



IMPORTANT DISCLAIMER

CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.

USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.

USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.