Place Details - Fetch
Retrieve Google Places place detail using SQL Server
- See SQLHTTP easy setup for Google Places
- See API Call documentation for parameter values and other information
- Create the stored procedure documented below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
CREATE PROCEDURE usp_Google_Places_Place_Details_Search( @Profile varchar(100), @place_id nvarchar(100), @language nvarchar(5) = NULL, @region nvarchar(2) = 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/details/xml' EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'placeid', @place_id, 'language', @language, 'region', @region, '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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) EXEC usp_Google_Places_Place_Details_Search @Profile = 'My Google Places', @place_id = 'ChIJefEUqskhTIYR9DR-h11qUrU', @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, 'PlaceDetailsResponse' -- --EXEC SQLHTTP.net.XQueryHelper @X, 'PlaceDetailsResponse/result' -- DECLARE @status nvarchar(MAX) SELECT @status = T.C.value(N'status[1]', N'nvarchar(MAX)') FROM @X.nodes(N'/PlaceDetailsResponse') T(C) IF @status <> 'OK' BEGIN RAISERROR('@Status', 16, 1) RETURN END SELECT T.C.value(N'id[1]', N'nvarchar(MAX)') AS [id] ,T.C.value(N'name[1]', N'nvarchar(MAX)') AS [name] ,T.C.value(N'vicinity[1]', N'nvarchar(MAX)') AS [vicinity] ,T.C.value(N'geometry[1]/location[1]/lat[1]', N'nvarchar(MAX)') AS [lat] ,T.C.value(N'geometry[1]/location[1]/lng[1]', N'nvarchar(MAX)') AS [lng] ,T.C.value(N'rating[1]', N'nvarchar(MAX)') AS [rating] FROM @X.nodes(N'/PlaceDetailsResponse/result') T(C) END |
1 2 3 4 5 |
id name vicinity lat lng rating ---------------------- ----------- ---------------------- ----------- ------------ --------- 34e0f55c2a87bea380... Big Shucks 103 South Coit Road... 32.9489231 -96.7684847 4.1 |
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.