SQL Server CLR 資料表值函數：即時抓取網路 XML 天氣服務

示範建立 **CLR Table-Valued Function (TVF)**，於查詢時即時呼叫網路 API（網易天氣 XML），解析並回傳結構化資料列集，實現「資料庫即服務」的跨界整合。

CLR TVF 實作要點

• 標記 `[SqlFunction(FillRowMethodName="FillRow", TableDefinition="...")]`。
• 主函數回傳 `IEnumerable`（實作 `List`）。
• `FillRow` 方法將自訂物件拆解為輸出欄位。
• 使用 `WebClient.DownloadData` 取得 XML，`XmlDocument` 解析節點屬性。

C# 程式碼

using System; using System.Collections.Generic; using System.Data.SqlTypes;
using Microsoft.SqlServer.Server; using System.Net; using System.Xml; using System.Text;

public partial class UserDefinedFunctions
{
    [SqlFunction(TableDefinition = "city NVARCHAR(100), date NVARCHAR(100), general NVARCHAR(100), temperature NVARCHAR(100), wind NVARCHAR(100)",
                 FillRowMethodName = "FillRow", Name = "GetWeather")]
    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 = Encoding.Default.GetString(data);
        XmlDocument doc = new XmlDocument(); doc.LoadXml(xml);
        foreach (XmlNode province in doc.ChildNodes[1].ChildNodes)
        {
            string city = province.Attributes["name"]?.Value;
            foreach (XmlNode day in province.ChildNodes)
            {
                list.Add(new Item {
                    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)
    {
        Item it = (Item)obj;
        city = it.city; date = it.date; general = it.general; temperature = it.temperature; wind = it.wind;
    }

    class Item { public string city, date, general, temperature, wind; }
}

部署 T-SQL

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();

結果欄位：city, date, general, temperature, wind。可直接 `JOIN` 本地資料表進行豐富分析。

安全與效能提醒

• 需 `PERMISSION_SET = UNSAFE`（網路存取、非受控組件）。
• 生產環境建議：
  ① 快取 API 回應（如用 SQL Agent 定期寫入本地表）。
  ② 設定 `HttpWebRequest.Timeout` 避免阻塞。
  ③ 考慮改用 `sp_OACreate` + `MSXML2.XMLHTTP` 純 T-SQL 方案（較繁瑣但無需 CLR）。