SQL2005CLR函数扩展-解析天气服务的实现（sqlldr 函数）不要告诉别人

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);

                }

            }

        //}

        //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;

        }

    }

};