使用XML向SQL Server 2005批量写入数据——一次有关XML时间格式的折腾经历
常常遇到需要向SQL Server插入批量数据,然后在存储过程中对这些数据进行进一步处理的情况。存储过程并没有数组、列表之类的参数类型,使用XML类型可妥善解决这个问题。
不过,SQL Server2005对标准xml的支持不足,很多地方需要特别处理。举一个例子说明一下。
这个场景是往存储过程里传递一个xml序列化了的List<Model>。
1.Model的代码如下,这是一个实体类
public class Model { /// <summary> /// UIN /// </summary> [XmlElement("UIN")] public long UIN { get; set; } /// <summary> /// 昵称 /// </summary> [XmlElement("Name")] public string Name { get; set; } /// <summary> /// 头像 /// </summary> [XmlElement("Img")] public string Img { get; set; } /// <summary> /// 访问时间 /// </summary> [XmlElement("VisitTime")] public DateTime VisitTime { get; set; } }
然后我们需要将这个List<Model>序列化成一个xml的字符串。但是SQL Server对xml的命名空间识别是有问题的,.net默认的序列化会出现xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd=http://www.w3.org/2001/XMLSchema
有网友给出了一个完美序列化Sql Server2005支持的xml的类(参考http://www.cnblogs.com/prime/archive/2012/10/11/SQLXML.html):
public static class DbXml { private static readonly XmlSerializerNamespaces Namespaces = new XmlSerializerNamespaces(); static DbXml() { //去掉 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Namespaces.Add(string.Empty, string.Empty); } /// <summary> /// 把一个对象序列化成一个Xml字符串 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="obj"></param> /// <returns></returns> public static string SerializeXml<T>(T obj) { XmlSerializer serializer = new XmlSerializer(typeof(T)); using (MemoryStream stream = new MemoryStream()) { serializer.Serialize(stream, obj, Namespaces); return Encoding.UTF8.GetString(stream.ToArray()); } } public static T DeserializeXml<T>(string obj) { XmlSerializer serializer = new XmlSerializer(typeof(T)); using (StringReader reader = new StringReader(obj)) { return (T)serializer.Deserialize(reader); } } }
使用的时候只需要:string xml = DbXml.SerializeXml<List<QQVisitorXml>>(list) 即可获取序列化后的xml字符串:
<?xml version="1.0"?> <ArrayOfModel> <Model> <UIN>0</UIN> <Name>name0</Name> <Img>img0</Img> <VisitTime>2009-07-17T00:00:00-05:00</VisitTime> </Model> <Model> <UIN>1</UIN> <Name>name1</Name> <Img>img1</Img> <VisitTime>2009-07-17T00:00:00-05:00</VisitTime> </Model> <Model> <UIN>2</UIN> <Name>name2</Name> <Img>img2</Img> <VisitTime>2009-07-17T00:00:00-05:00</VisitTime> </Model> </ArrayOfModel>
2.存储过程里,读取xml到一个临时表#temp里:
select c.value(‘(UIN)[1]‘,‘varchar(30)‘) as uin, c.value(‘(Name)[1]‘,‘varchar(50)‘) as Name, c.value(‘(Img)[1]‘,‘varchar(200)‘) as Img, c.value(‘(VisitTime)[1]‘,‘datetime‘) as VisitTime into #temp from @strxml.nodes(‘//Model‘) T(c) --@strxml是存储过程的xml参数
然后就可以对#temp按照普通表进行进一步处理。
我们试着执行这个存储过程。嗯?出错了?!
3.原来,XML的时间标准格式是”年-月-日T时:分:秒-时区” SQL Server2005不支持时区,所以它也不能支持xml的时间格式(倒是支持年-月-日T时:分:秒)。这个问题在SQL server 2008中得到改进,完整支持了xml的时间格式。但是我们数据库是2005,没办法,得想个办法解决。解决办法是把时间字转成字符串,然后截取 年-月-日T时:分:秒,最后再加上东八区的时区数,这样sql修正为:
select c.value(‘(UIN)[1]‘,‘varchar(30)‘) as uin, c.value(‘(Name)[1]‘,‘varchar(50)‘) as Name, c.value(‘(Img)[1]‘,‘varchar(200)‘) as Img, dateadd(hour,8,convert(datetime,left(t.c.value(‘(VisitTime)[1]‘,‘varchar(30)‘), 19),127)) as VisitTime into #temp from @strxml.nodes(‘//Model‘) T(c) --@strxml是存储过程的xml参数
本地测试,成功!
4.放到服务器上测试,执行倒是成功了,可以一查看数据,又出问题了!服务器上插入数据表的时间,和我本地测试数据库的时间,相差8个小时!本地开发环境是windows8,服务器是windows server 2008。开发环境和服务器环境有差异,导致本地获取xml带时区,服务器不带时区。
过于依赖环境,就太危险了!果断放弃时间格式,修改Model中时间为字符串:
public class Model { /// <summary> /// UIN /// </summary> [XmlElement("UIN")] public long UIN { get; set; } /// <summary> /// 昵称 /// </summary> [XmlElement("Name")] public string Name { get; set; } /// <summary> /// 头像 /// </summary> [XmlElement("Img")] public string Img { get; set; } /// <summary> /// 访问时间 /// </summary> [XmlIgnore] //xml序列化时跳过 public DateTime VisitTime { get; set; } [XmlElement("VisitTime")] public string XVisitTime { get { return this.VisitTime.ToString("yyyy-MM-dd HH:mm:ss"); } set { this.VisitTime = DateTime.Parse(value); } } }
在存储过程中把这个时间字符串转换成时间:
select c.value(‘(UIN)[1]‘,‘varchar(30)‘) as uin, c.value(‘(Name)[1]‘,‘varchar(50)‘) as Name, c.value(‘(Img)[1]‘,‘varchar(200)‘) as Img, convert(datetime,c.value(‘(VisitTime)[1]‘,‘varchar(30)‘)) as VisitTime into #temp from @strxml.nodes(‘//Model‘) T(c)
Ok。所有问题都解决了,畅快。
使用XML向SQL Server 2005批量写入数据——一次有关XML时间格式的折腾经历,古老的榕树,5-wow.com
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。