I really loved the article!

So I tried the same with the NS api (www.ns.nl/api), but when I put the url in firefox, I get results, but within my stored procedure I get @XML = null.

I do have to send parameters with my login details, but I don't think that would be the problem.

Here's my code (without username and password):

CREATE PROCEDURE [dbo].[spNS]

AS

BEGIN

SET NOCOUNT ON

DECLARE @URL varchar(MAX)

-- SET @URL = 'http://webservices.ns.nl/ns-api-treinplanner?fromStation=Utrecht+Centraal&toStation=Wierden&departure=true'

SET @URL = 'https://username:password@webservices.ns.nl/ns-api-treinplanner?fromStation=Arnhem&toStation=Wierden&departure=true'

DECLARE @Response varchar(8000)

DECLARE @XML xml

DECLARE @Obj int

DECLARE @Result int

DECLARE @HTTPStatus int

DECLARE @ErrorMsg varchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT

BEGIN TRY

EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false

EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'

EXEC @Result = sp_OAMethod @Obj, send, NULL, ''

EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT

EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml', @Response OUT

END TRY

BEGIN CATCH

SET @ErrorMsg = ERROR_MESSAGE()

END CATCH

EXEC @Result = sp_OADestroy @Obj

IF (@ErrorMsg IS NOT NULL) OR (@HTTPStatus <> 200) BEGIN

SET @ErrorMsg = 'Error in spNS: ' + ISNULL(@ErrorMsg, 'HTTP result is: ' + CAST(@HTTPStatus AS varchar(10)))

RAISERROR(@ErrorMsg, 16, 1, @HTTPStatus)

RETURN

END

SET @XML = CAST(@Response AS XML)

-- SET @GPSLatitude = @XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'numeric(9,6)')

-- SET @GPSLongitude = @XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'numeric(9,6)')

SELECT @Response AS XMLResults

END