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

我们可以用clr获取网络服务 来显示到数据库自定函数的结果集中，比如163的天气预报
他的这个xml结果的日期是不正确的，但这个我们暂不讨论。
从这个xml获取天气的clr代码如下，用webclient访问一下就可以了。然后通过dom对象遍历节点属性返回给结果集。
--------------------------------------------------------------------------------

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 <item > 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 <item > getdata()
    {
        system.collections.generic.list <item > ret = new list <item >();
        //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);
                }
            }

        //}
        //catch(exception ex)
        //{
        //    sqlcontext.pipe.send(ex.message);
        //}
        return ret;
    }

    static string getxmlattrib(system.xml.xmlnode node, string attrib)
    {
        try
        {
            return node.attributes[attrib].value;
        }
        catch
        {
            return string .empty;
        }
    }
};

--------------------------------------------------------------------------------
部署这个clr函数的脚本如下
--------------------------------------------------------------------------------

drop function dbo. xfn_getweather
drop   assembly testweather
go
create assembly testweather from 'd:/sqlclr/testweather.dll' with permission_set = unsafe;
--
go
create function dbo. xfn_getweather ()    
returns table ( city nvarchar ( 100), date nvarchar ( 100), general nvarchar ( 100), temperature nvarchar ( 100), wind nvarchar ( 100))
as external name testweather. userdefinedfunctions. getweather

--------------------------------------------------------------------------------
测试函数
--------------------------------------------------------------------------------
select * from dbo. xfn_getweather ()