Recently we released an update to EDIS to do a web service call and get the results back to a table in XML or JSON format. I didn’t realize at the time that this feature could evolve to do so much, but basically any website that has an OData API was fair game to try the web service against. As a test, I created a stored proc that leveraged the web service tool to get a stock price quote from yahoo finance. Below is the code. You just pass in the stock ticker symbol, and EDIS does the rest quick and easy. To try this, there is a free demo version of EDIS at http://www.sqletl.com for download.
CREATE proc [dbo].[usp_get_stock_price]
declare @url varchar(max) = ‘http://finance.yahoo.com/webservice/v1/symbols/’+@stock_ticker+’/quote?format=xml’
@url = @url
,@output_tbl_nm = ‘##quote_price’
declare @res xml = cast((select * from ##quote_price) as xml);
declare @curr_price varchar(50) = @res.value(‘(list/resources/resource/field[@name=”price”]) ’,’varchar(50)’)