我们可以用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 ()