SQL使用WebService取得資料

以前從沒想過SQL SERVER可以使用WebService取得資料，最近因工作需要看到了SSIS可以做到(SSIS-如何接收web services的資料當做資料來源做轉檔)，但是發現我需要使用的WebService傳入參數是他自己指定的Type，我從資料庫取出資料再指定變數給WebService會發生不認識的情況而無法正常取值，後來無意間發現了原來SQL SERVER 2005之後就可以用SQL語法直接使用WebService。

declare @sUrl varchar(200)
set @sUrl='http://www.webservicex.net/CurrencyConvertor.asmx/ConversionRate?FromCurrency=USD&ToCurrency=TWD'  --設定Url

Declare @Object as Int; --宣告物件
Declare @ResponseText as Varchar(max); --宣告回傳資料

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; --創造物件
Exec sp_OAMethod @Object, 'open', NULL, 'get',@sUrl,false  --開啟
Exec sp_OAMethod @Object, 'send'  --執行
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT   --取回資料
Exec sp_OADestroy @Object   --釋放
Select @ResponseText

這樣就可以取得WebService所回傳的資料，但回傳的是XML格式，所以還需要使用XQuery把值取出來。
因為這個WebService只有回傳一個Double的數值，不需要很複雜的指令，最後再Insert到Table 搞定

declare @sUrl varchar(200)
declare @xml xml --宣告XML變數
set @xml=@ResponseText --放資料
select convert(varchar,@xml.query('data(/)')) --XML要先轉字串，沒辦法直接指定其他型別

以上
目前沒時間詳細研究XQuery，日後有機會再玩玩吧
回sql目錄
回首頁