dimanche 21 août 2016

Procedure result with XML parsing error

Hi guys I'm trying to use my first procedure. I need it for Google Matrix. This is my procedure, found there

CREATE PROCEDURE [dbo].[CalculateDistanceGoogle] ( @ToAddress NVARCHAR(100) , @FromAddress NVARCHAR(100), @DistanceistanceInKm FLOAT OUTPUT ) AS

BEGIN

DECLARE @Object INT DECLARE @ResponseonseText NVARCHAR(4000) DECLARE @StatuserviceUrl NVARCHAR(500)

SET @StatuserviceUrl = 'http://ift.tt/19WUt3A' + @ToAddress + '&destinations=' + @FromAddress +'&mode=driving&language=it-IT&units=metric;'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; EXEC sp_OAMethod @Object, 'open', NULL, 'get', @StatuserviceUrl, 'false' EXEC sp_OAMethod @Object, 'send' EXEC sp_OAMethod @Object, 'responseText', @ResponseonseText OUTPUT

DECLARE @Response XML

SET @Response = CAST(CAST(@ResponseonseText AS NVARCHAR(MAX)) AS XML)

DECLARE @Status NVARCHAR(20) DECLARE @Distance NVARCHAR(20)

SET @Status = @Response.value('(DistanceMatrixResponse/row/element/status)[1]', 'NVARCHAR(20)')

IF(@Status = 'ZERO_RESULTS') SET @Distance = NULL ELSE SET @Distance = @Response.value('(DistanceMatrixResponse/row/element/distance/value)[1]', 'NVARCHAR(20)')

SET @DistanceistanceInKm = ROUND(CAST(@Distance AS FLOAT) / 1000, 1)

PRINT @DistanceistanceInKm

END

And this is the execution to store result of distance in the variable:

DECLARE @Distanza NVARCHAR 
EXEC @Distanza = dbo.CalculateDistanceGoogle @ToAddress = 'CITY', @FromAddress='CITY',  @DistanceistanceInKm = @Distanza
PRINT @Distanza

I've this error:

Msg 9402, Level 16, State 1, Procedure CalculateDistanceGoogle, Line 13 XML parsing: line 1, character 38, unable to switch the encoding

Can you help me to understand why?

Thank you!

Aucun commentaire:

Enregistrer un commentaire