通过 SQL Server 视图公开基于 Web 的 XML 数据

Question

看起来 SQL Server 有相当多的 XML 支持。我主要看到过有关在 SQL Server 中存储 XML、查询 SQL Server 中存储的 XML 数据以及将数据公开为 XML 的信息。

以下场景是一个选项吗：

我想通过 SQL Server 视图从网站公开 xml 数据（它是工作项的 RSS 视图）。动机是创建新的计算值，然后通过 SSRS 报告显示数据。

我想使用视图，以便数据始终处于活动状态，并避免需要批量 ETL。

这可能吗？语法是什么样的？

Answer 1

创建一个 CLR 过程来提取实时源

Answer 2

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.IO;
using System.Net;
using System.Runtime.InteropServices;

using System.Xml;

namespace RSSFunctions
{
    public class GetRSSFeedClass
    {
        private class RSSRow
        {
            public SqlString Title;
            public SqlString Description;

            public RSSRow(SqlString Title, SqlString Description)
            {
                this.Title = Title;
                this.Description = Description;
            }
        }

        [SqlFunction(FillRowMethodName = "FillRSSRow")]
        public static IEnumerable GetRSSFeed(SqlString RSSurl)
        {
            ArrayList RSSRowsCollection = new ArrayList();
            string url = RSSurl.ToString();
            WebRequest req = System.Net.WebRequest.Create(url);
            WebResponse Res = req.GetResponse();

            Stream rssStream = Res.GetResponseStream();
            XmlDocument rssDoc = new XmlDocument();
            rssDoc.Load(rssStream);

            XmlNodeList rssItems = rssDoc.SelectNodes("rss/channel/item");

            String Title = "";
            String Description = "";
            int i = 0;

            for (i = 0; i <= rssItems.Count - 1; i++)
            {
                XmlNode rssDetail = default(XmlNode);

                Title = "";
                rssDetail = rssItems.Item(i).SelectSingleNode("title");
                if (rssDetail.Equals(null) == false)
                {
                    Title = rssDetail.InnerText;
                }

                Description = "";
                rssDetail = rssItems.Item(i).SelectSingleNode("description");
                if (rssDetail.Equals(null) == false)
                {
                    Description = rssDetail.InnerText;
                }

                if (Title.Length > 97)
                {
                    Title = Title.Substring(0, 97) + "...";
                }

                if (Description.Length > 3997)
                {
                    Description = Description.Substring(0, 3997) + "...";
                }

                if (!string.IsNullOrEmpty(Title) && !string.IsNullOrEmpty(Description))
                {
                    RSSRowsCollection.Add(new RSSRow(new SqlString(Title), new SqlString(Description)));

                }
            }

            return RSSRowsCollection;
        }

        public static void FillRSSRow(object obj, out SqlString Title, out SqlString Description)
        {
            RSSRow _RSSRow = (RSSRow)obj;
            Title = _RSSRow.Title;
            Description = _RSSRow.Description;
        }

SSMS

--ALTER DATABASE [dbname] TRUSTWORTHY ON
--go

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'RSSData')
   DROP VIEW RSSData
go

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'fncCLRGetRSSFeed')
   DROP FUNCTION fncCLRGetRSSFeed
go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'CLRRSSAssembly')
   DROP ASSEMBLY CLRRSSAssembly
go
CREATE ASSEMBLY CLRRSSAssembly FROM 'C:\RSSAssembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

CREATE FUNCTION fncCLRGetRSSFeed(@url nvarchar(100)) 
RETURNS TABLE (
   Title nvarchar(100),
   [Description] nvarchar(4000)
)
AS EXTERNAL NAME CLRRSSAssembly.[RSSFunctions.GetRSSFeedClass].GetRSSFeed
go

CREATE VIEW RSSData
AS
SELECT * FROM fncCLRGetRSSFeed(N'http://channel9.msdn.com/Feeds/RSS/')
go

SELECT * FROM RSSData