Dynamic XML connection string

Question

Dynamic XML connection string

craig.donoghue 48563

Mr or Mrs. 500

Points: 524
More actions
March 31, 2015 at 4:45 pm

#296527

Hi,
I'm trying to use OpenWeatherMaps to pull weather forecast data into a report, which is working fine if I hard code the XML connection string, i.e
http://api.openweathermap.org/data/2.5/weather?lat=35&lon=139&mode=xml&APPID=xxxxxx
However, I wanted to pass latitude/longitude values via report parameters to the connections string, i.e
="http://api.openweathermap.org/data/2.5/weather?lat=" & Parameters!Latitude.Value & "&lon=" & Parameters!Longitude.Value & "&mode=xml&APPID=xxxxxx"
This gives me the following error;
"An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'Weather'. (rsErrorExecutingCommand)
Failed to prepare web request for the specified URL. (rsXmlDataProviderError)
Invalid URI: The URI scheme is not valid."
Is it possible to pass an XML connection string this way or is there another way to do this?
Appreciate any help/suggestions anyone may have.
Thanks,
Craig

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dominicrusso SSC Rookie Points: 26 More actions · Answer 1

I'm actually trying to use the same API now too. How do you create a dataset to pull in the data? I have the XML datasource set up with the appid and everything, but Im' not getting any data back when I run the query in the query designer.

Thanks!

craig.donoghue 48563 Mr or Mrs. 500 Points: 524 More actions · Answer 2

Hi,

Set up a SSRS report with lon and lat parameters. Then add a data source as XML type with the following connection string formula;

="http://api.openweathermap.org/data/2.5/weather?mode=xml&units=metric&APPID=yourAPPID&lat=" & Parameters!Latitude.Value & "&lon=" & Parameters!Longitude.Value

Then add a data source to query the XML. The below will pull out the current, maximum and minimum temp, plus the chance of rain.

<Query>

<ElementPath>

es:current{city{@name},temperature{@value, @min-2, @max-2}, clouds{@name, @value}, precipitation{@unit, @value, @mode}, lastupdate{@value}, weather{@value, @icon}}

/wind{speed{@name, @value}

,direction{@name, @value, @code}

}

</ElementPath>

</Query>

I know little about XML query, but I pieced this together from looking at examples online.

dominicrusso SSC Rookie Points: 26 More actions · Answer 3

Perfect! I was stuck on that query as well and found too many examples I wasn't sure where to start

Joshua42 Newbie Points: 3 More actions · Answer 4

Did you ever solve this error:

Failed to prepare web request for the specified URL. (rsXmlDataProviderError)

Joshua42 Newbie Points: 3 More actions · Answer 5

Joshua42

Newbie

Points: 3

January 22, 2020 at 8:26 pm

#3717556

Did you ever solve this error?

Dynamic XML connection string

Cookies on SQLServerCentral