Exposing web based XML data via a SQL Server view

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?

Answers


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

Create a CLR proc to pull the live feed


Need Your Help

What is the standard way to handle url-mapping for JavaServer Faces?

java jsf java-ee

All the tutorials I have seen seem to use *.jsf, *.faces, or /&lt;directory_name&gt;/* for the url-pattern in the Java Server faces servlet mapping. Is there some official sun-recommended naming

Why is an index not used on a LIKE query with wildcards?

mysql explain

Although the Title column was added as index by using the following query: