SQL2005CLR函数扩展-解析天气服务的实现

我们可以用CLR获取网络服务 来显示到数据库自定函数的结果集中，比如163的天气预报
http://news.163.com/xml/weather.xml
<FONT style=”COLOR: #ff0000″>他的这个xml结果的日期是不正确的，但这个我们暂不讨论。
从这个xml获取天气的CLR代码如下，用WebClient访问一下就可以了。然后通过Dom对象遍历节点属性返回给结果集。
——————————————————————————–
<div class=”codetitle”><a style=”CURSOR: pointer” data=”14017″ class=”copybut” id=”copybut14017″ onclick=”doCopy(‘code14017’)”> 代码如下:<div class=”codebody” id=”code14017″>
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
using System.Collections.Generic;
using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions
{ [SqlFunction (TableDefinition = “city nvarchar(100),date nvarchar(100),general nvarchar(100),temperature nvarchar(100),wind nvarchar(100)”,Name = “GetWeather”,FillRowMethodName = “FillRow” )]
public static IEnumerable GetWeather()
{
System.Collections.Generic.List list = GetData();
return list;
}
public static void FillRow(Object obj,out SqlString city,out SqlString date,out SqlString general,out SqlString temperature,out SqlString wind)
{
Item data = (Item )obj;
city = data.city;
date = data.date;
general = data.general;
temperature = data.temperature;
wind = data.wind;
} class Item
{
public string city;
public string date;
public string general;
public string temperature;
public string wind;
}
static System.Collections.Generic.List GetData()
{
System.Collections.Generic.List ret = new List ();
//try
//{ string url = “http://news.163.com/xml/weather.xml” ;
System.Net.WebClient wb = new System.Net.WebClient ();
byte [] b = wb.DownloadData(url);
string data = System.Text.Encoding .Default.GetString(b);
System.Xml.XmlDocument doc = new System.Xml.XmlDocument ();
doc.LoadXml(data); foreach (System.Xml.XmlNode node in doc.ChildNodes[1])
{
string city = GetXMLAttrib(node,”name” );
foreach (System.Xml.XmlNode subnode in node.ChildNodes)
{
Item item = new Item ();
item.city = city;
item.date = GetXMLAttrib(subnode,”date” );
item.general = GetXMLAttrib(subnode,”general” );
item.temperature = GetXMLAttrib(subnode,”temperature” );
item.wind = GetXMLAttrib(subnode,”wind” );
ret.Add(item);
}
} /