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

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

Answer 2

Create a CLR proc to pull the live feed

友情链接