SQL CLR 不僅能做運算,還能呼叫外部 Web Service,將結果直接作為資料表回傳。本文示範建立一個 **Table-Valued Function (TVF)**,透過 WebClient 取得網易天氣 XML,解析後以 IEnumerable + FillRow 模式回傳多欄位結果集,可直接在 SELECT * FROM dbo.xfn_GetWeather() 中使用。
using System;
using System.Collections.Generic;
using System.Net;
using System.Xml;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public partial class UserDefinedFunctions
{
[SqlFunction(
TableDefinition = "city NVARCHAR(100), date NVARCHAR(100), general NVARCHAR(100), temperature NVARCHAR(100), wind NVARCHAR(100)",
Name = "GetWeather",
FillRowMethodName = "FillRow")]
public static IEnumerable GetWeather()
{
var list = new List();
string url = "https://news.163.com/xml/weather.xml";
byte[] data = new WebClient().DownloadData(url);
string xml = System.Text.Encoding.Default.GetString(data);
var doc = new XmlDocument();
doc.LoadXml(xml);
foreach (XmlNode province in doc.ChildNodes[1]) // 跳過 XML 宣告節點
{
string city = province.Attributes["name"]?.Value;
foreach (XmlNode day in province.ChildNodes)
{
list.Add(new WeatherItem
{
City = city,
Date = day.Attributes["date"]?.Value,
General = day.Attributes["general"]?.Value,
Temperature = day.Attributes["temperature"]?.Value,
Wind = day.Attributes["wind"]?.Value
});
}
}
return list;
}
public static void FillRow(object obj,
out SqlString city, out SqlString date, out SqlString general,
out SqlString temperature, out SqlString wind)
{
var item = (WeatherItem)obj;
city = item.City; date = item.Date; general = item.General;
temperature = item.Temperature; wind = item.Wind;
}
class WeatherItem
{
public string City, Date, General, Temperature, Wind;
}
}
DROP FUNCTION IF EXISTS dbo.xfn_GetWeather;
DROP ASSEMBLY IF EXISTS TestWeather;
GO
CREATE ASSEMBLY TestWeather FROM 'D:\sqlclr\TestWeather.dll' WITH PERMISSION_SET = UNSAFE;
GO
CREATE FUNCTION dbo.xfn_GetWeather()
RETURNS TABLE (
city NVARCHAR(100), date NVARCHAR(100), general NVARCHAR(100),
temperature NVARCHAR(100), wind NVARCHAR(100))
AS EXTERNAL NAME TestWeather.UserDefinedFunctions.GetWeather;
GO
SELECT * FROM dbo.xfn_GetWeather();

FillRow 的 out 參數一一對應。IEnumerable 逐列產生 (Streaming TVF)。UNSAFE;生產環境建議改用應用層定時抓取寫入資料表,或用 PolyBase/外部資料表 (SQL 2016+)。sp_invoke_external_rest_endpoint 直接呼叫 HTTP 端點。