atif_jatt Ответов: 0

Как я могу прочитать XML из URL-адреса с помощью SQL server

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


Что я уже пробовал:

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

0 Ответов