SQL Server 2005 CLR 函數：串接網路服務獲取天氣資料

SQL CLR 不僅能處理資料庫內部運算，更可呼叫外部 Web Service 並將結果直接以資料表值函數 (TVF) 回傳，讓 T-SQL 查詢能無縫整合外部資料。本文以網易 (163) 天氣 XML 服務為例，示範完整實作流程。

資料來源

目標 API：https://news.163.com/xml/weather.xml (註：原始資料日期欄位可能不準，僅供技術示範)。

CLR 表值函數實作 (C#)

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

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()
    {
        return GetData();
    }

    public static void FillRow(object obj, out SqlString city, out SqlString date, out SqlString general, out SqlString temperature, out SqlString wind)
    {
        Item data = (Item)obj;
        city = data.City; date = data.Date; general = data.General;
        temperature = data.Temperature; wind = data.Wind;
    }

    private class Item
    {
        public string City, Date, General, Temperature, Wind;
    }

    private static List GetData()
    {
        var list = new List();
        try
        {
            string url = "https://news.163.com/xml/weather.xml";
            using (WebClient wc = new WebClient())
            {
                byte[] data = wc.DownloadData(url);
                string xml = System.Text.Encoding.Default.GetString(data);
                XmlDocument doc = new XmlDocument();
                doc.LoadXml(xml);

                foreach (XmlNode cityNode in doc.ChildNodes[1]) // 假設第二層為城市節點
                {
                    string city = GetXmlAttribute(cityNode, "name");
                    foreach (XmlNode dayNode in cityNode.ChildNodes)
                    {
                        list.Add(new Item
                        {
                            City = city,
                            Date = GetXmlAttribute(dayNode, "date"),
                            General = GetXmlAttribute(dayNode, "general"),
                            Temperature = GetXmlAttribute(dayNode, "temperature"),
                            Wind = GetXmlAttribute(dayNode, "wind")
                        });
                    }
                }
            }
        }
        catch (Exception ex)
        {
            SqlContext.Pipe.Send("錯誤: " + ex.Message);
        }
        return list;
    }

    private static string GetXmlAttribute(XmlNode node, string attrName)
    {
        try { return node.Attributes[attrName].Value; }
        catch { return string.Empty; }
    }
}

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

執行後將直接在 SSMS 結果格線中顯示各城市天氣資料，可直接加入 WHERE 子句篩選特定城市或日期。

實務應用提醒

• 權限設定：組件需設為 UNSAFE 才能進行網路存取。
• 例外處理：生產環境務必完善 try-catch，避免外部服務異常導致查詢中斷。
• 快取機制：可加入記憶體快取 (如 MemoryCache) 避免頻繁呼叫外部 API。
• 安全性：若呼叫內部服務，建議使用 Windows 整合驗證或憑證驗證，避免硬編碼帳密。