Read RSS feed by using SQL Server stored procedure

If you want to read RSS Feed by using SQL Server stored procedure, you can use CLR stored procedure as described below:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Text;
using System.Xml.XPath;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void spReadRSSFeed(string strURL)
  {
      XPathDocument doc = new XPathDocument(strURL);
      XPathNavigator nav = doc.CreateNavigator();
      XPathNodeIterator i = nav.Select("//item");
      SqlMetaData[] rss_results = new SqlMetaData[4];
      rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
      rss_results[1] = new SqlMetaData("Publication Date",
          SqlDbType.DateTime);
      rss_results[2] = new SqlMetaData("Description",
          SqlDbType.NVarChar, 2000);
      rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);
      SqlDataRecord record = new SqlDataRecord(rss_results);
      SqlPipe sqlpipe = SqlContext.Pipe;
      sqlpipe.SendResultsStart(record);
      while (i.MoveNext())
      {
          record.SetString(0, (string)
              i.Current.Evaluate("string(title[1]/text())"));
          record.SetDateTime(1, DateTime.Parse((string)
              i.Current.Evaluate("string(pubDate[1]/text())")));
          record.SetString(2, (string)
              i.Current.Evaluate("string(description[1]/text())"));
          record.SetString(3, (string)
              i.Current.Evaluate("string(link[1]/text())"));
          sqlpipe.SendResultsRow(record);
      }
      sqlpipe.SendResultsEnd();
  }
};

After building to an assembly named RSSReader, for example, you can deploy it to your SQL Server like this:

CREATE ASSEMBLY [RSSReader]
AUTHORIZATION [dbo]
FROM 'Path to your RSSReader.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
-- Note that you have to use EXTERNAL_ACCESS because we need to access Internet
GO
CREATE PROCEDURE [dbo].[spReadRSSFeed]
@URL [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [RSSReader].[StoredProcedures].[spReadRSSFeed];

-- Running for testing
spReadRSSFeed 'http://msdn.microsoft.com/sql/rss.xml'

-- Remove them
DROP PROCEDURE spReadRSSFeed
DROP ASSEMBLY RSSReader