Exposing web based XML data via a SQL Server view

Question

It seems that SQL Server has a fair amount of XML support. Mostly I've seen info regarding storing XML in SQL Server, querying XML data stored in SQL Server, and exposing data as XML.

Is the following scenario an option:

I'd like to expose xml data (it's an RSS view of workitems) from a web site via a SQL Server view. The motivation is to create new computed values and then show the data via an SSRS report.

I'd like to use a view so that the data is always live, and avoid the need for a batch ETL.

Is this possible? What does the syntax look like?

Answer 1

Create a CLR proc to pull the live feed

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