Click here to Skip to main content
13,732,880 members
Rate this:
 
Please Sign up or sign in to vote.
I created below function to retrieve data from XML. I want to get formatted_address from the generated XML. How to get required result? I got NULL value. I want to get required result only using function


What I have tried:

CREATE FUNCTION [dbo].[GetLocation]
     (@latt VARCHAR(150), @lont VARCHAR(150)) 
RETURNS varchar(max) 
AS 
BEGIN 
    DECLARE @output NVARCHAR(MAX)
    DECLARE @URL VARCHAR(8000) 
    DECLARE @Response NVARCHAR(1024)
    DECLARE @XML XML
    DECLARE @Obj INT 
    DECLARE @Result INT
    DECLARE @HTTPStatus INT
    DECLARE @ErrorMsg VARCHAR(MAX)
    DECLARE @ConvertVar VARCHAR(8000)
    DECLARE @lat VARCHAR(150) 
    DECLARE @lon VARCHAR(150)

    SET @lat = @latt
    SET @lon = @lont

    SET @URL ='https://maps.googleapis.com/maps/api/geocode/xml?latlng=23.56320001,46.66140002'

    EXEC sp_OACreate 'MSXML2.XMLHttp', @obj OUT
    EXEC sp_OAMethod @obj, 'open', NULL, 'GET', @URL, false
    EXEC sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
    EXEC sp_OAMethod @Obj, send, NULL, ''
    EXEC sp_OAGetProperty @obj, 'ResponseText', @Response OUTPUT

    SET @Response = replace(@Response, '<?xml version="1.0" encoding="UTF-8" ?>', '')    
    SET @XML = convert(xml,@Response)
    SET @output = (SELECT   
                       T.c.value('formatted_address[1]','nvarchar(500)')                    
                   FROM   
                       @XML.nodes('reversegeocode/result[1]') T(c))

    RETURN @output
END
Posted 2-Nov-17 18:48pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180920.1 | Last Updated 3 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100