Transact-SQL 呼叫 RESTful Web Service

隨著雲端時代的來臨，RESTful Web Service 也日益盛行；這裡提供一種以 T-SQL 指令呼叫此種 Web Service 的方法，供各位參考。


本例將呼叫 YAHOO Weather 的 Restful Web Service，讀取台北的氣溫數據 －

exec sp_configure 'show advanced options', 1;
reconfigure with override
go

sp_configure 'Ole Automation Procedures', 1;
reconfigure with override
go


declare @object         int,
        @responseText   varchar(8000)
 
exec sp_OACreate 'MSXML2.XMLHTTP', @object out

-- YAHOO Weather (台北)
exec sp_OAMethod @object, 'open', null, 'get', 'http://weather.yahooapis.com/forecastrss?w=2306179', 'false'
exec sp_OAMethod @object, 'send'
exec sp_OAMethod @object, 'responseText', @responseText output
 
select @responseText responseText
 
exec sp_OADestroy @object
go


sp_configure 'Ole Automation Procedures', 0;
reconfigure with override
go

exec sp_configure 'show advanced options', 0;
reconfigure with override
go