使用 SQL Server 2005中的 CLR 集成

将上面的示例改为返回来自 RSS 供给的信息，返回结果的代码如下所示（不包括未实现的方法）：

[SqlFunction]
public static ISqlReader tvf1()
{
   return (ISqlReader)new RssReader();
}

public class RssReader : ISqlReader
{
   SqlMetaData[] rss_results = null;
   XPathDocument doc;
   XPathNavigator nav;
   XPathNodeIterator i;

   // Construct helper class, initializing metadata for the results
   // reading from the RSS feed, creating the iterator
   public RssReader()
   {
     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);

     // Retrieve the RSS feed
     doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml");
     nav = doc.CreateNavigator();
     i = nav.Select("//item");
   }

   // # of columns returned by the function
   public int FieldCount { get { return rss_results.Length; } }

   // metadata for each of the columns
   public SqlMetaData GetSqlMetaData(int FieldNo)
      { return rss_results[FieldNo]; }

   // Read method positions the navigator iterator on next element
   public bool Read() { return i.MoveNext(); }

   // methods to return each column
   public Object GetValue(int FieldNo) 
   {
      switch (FieldNo)
      {
         case 0:
            return new SqlString((string)                   
                   i.Current.Evaluate("string(title[1]/text())"));
         case 1:
            return new SqlDateTime(DateTime.Parse( 
                   
(string)i.Current.Evaluate("string(pubDate[1]/text())")));
         case 2:
            return new SqlString((string)
                   i.Current.Evaluate("string(description[1]/text())"));
         case 3:
            return new SqlString((string) 
                   i.Current.Evaluate("string(link[1]/text())"));
      }

      return null;
   }

   public string GetString(int i) { return (string)GetValue(i); }
   public DateTime GetDateTime(int i) { return (DateTime)GetValue(i); }
   public SqlChars GetSqlCharsRef(int i) { 
      return new SqlChars((SqlString)GetValue(i)); }
   public SqlChars GetSqlChars(int i) { 
      return new SqlChars((SqlString)GetValue(i)); }
   public SqlDateTime GetSqlDateTime(int i) { 
      return (SqlDateTime)GetValue(i); }
   ...
}

使用来自此表值函数的结果的一种简单查询如下所示：

select title, pubdate, description, link from dbo.GetRssFeed()

显然可以用此数据的 TVF 形式来表示更丰富的查询。假定函数 CanonicalURL() 会返回规范的 URL 版本。现在，可以使用规范的 URL 很容易地返回来自 RSS 供给的数据：

select title, pubdate, description, dbo.CanonicalURL(link) 
from dbo.tvf1()
order by pubdate

	
	[1]
		[2]
		[3]
		[4]
		[5]
		[6]
		[7]
		[8]
		[9]
		[10]
		[11]
		[12]
		[13]