萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL CLR 表值函數整合網路服務:即時抓取網易天氣 XML 回傳結果集

SQL CLR 不僅能做運算,還能呼叫外部 Web Service,將結果直接作為資料表回傳。本文示範建立一個 **Table-Valued Function (TVF)**,透過 WebClient 取得網易天氣 XML,解析後以 IEnumerable + FillRow 模式回傳多欄位結果集,可直接在 SELECT * FROM dbo.xfn_GetWeather() 中使用。

CLR 代碼 (TestWeather.cs)

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;
    }
}

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

天氣 TVF 查詢結果

關鍵技術點

  • TableDefinition:定義回傳欄位名稱、型別、長度,必須與 FillRow 的 out 參數一一對應。
  • FillRowMethodName:將集合中每個物件拆解為資料列的靜態方法。
  • IEnumerable:CLR 先在記憶體建立完整集合再串流回傳,資料量極大時請改用 IEnumerable 逐列產生 (Streaming TVF)。
  • UNSAFE 權限:網路存取、XML 解析需 UNSAFE;生產環境建議改用應用層定時抓取寫入資料表,或用 PolyBase/外部資料表 (SQL 2016+)。

現代替代架構

  • **Azure Functions / Logic Apps** 定時抓取 → 寫入 Azure SQL / Blob。
  • **PolyBase** 建立外部資料表指向 REST API (需自建 ODBC 驅動)。
  • **SQL 2022+** sp_invoke_external_rest_endpoint 直接呼叫 HTTP 端點。
copyright © 萬盛學電腦網 all rights reserved