使用XML向SQL Server 2005批量写入数据——一次有关XML时间格式的折腾经历-阿里云开发者社区

开发者社区> 杰克.陈> 正文

使用XML向SQL Server 2005批量写入数据——一次有关XML时间格式的折腾经历

简介: 原文:使用XML向SQL Server 2005批量写入数据——一次有关XML时间格式的折腾经历 常常遇到需要向SQL Server插入批量数据,然后在存储过程中对这些数据进行进一步处理的情况。存储过程并没有数组、列表之类的参数类型,使用XML类型可妥善解决这个问题。
+关注继续查看
原文:使用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。所有问题都解决了,畅快。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PostgreSQL 使用逻辑decode实现异步主从切换后,时间线分歧变化量补齐、修复
PostgreSQL 使用逻辑decode实现异步主从切换后,时间线分歧变化量补齐、修复
407 0
Hadoop大数据挖掘从入门到进阶实战
1.概述   大数据时代,数据的存储与挖掘至关重要。企业在追求高可用性、高扩展性及高容错性的大数据处理平台的同时还希望能够降低成本,而Hadoop为实现这些需求提供了解决方案。面对Hadoop的普及和学习热潮,笔者愿意分享自己多年的开发经验,带领读者比较轻松地掌握Hadoop数据挖掘的相关知识。
1644 0
iOS中CoreData数据管理系列四——进行数据与页面的绑定
iOS中CoreData数据管理系列四——进行数据与页面的绑定
18 0
MySql数据库批量备份命令
rd d:\mysql_data-BAK /s /qmd d:\mysql_data-BAKxcopy d:\mysql_data d:\mysql_data-BAK /e
668 0
Observability:使用 Elastic Stack 分析地理空间数据
在今天的文章中,我们将参考之前的文章 “如何使用 Elasticsearch ingest 节点来丰富日志和指标”。我们可以利用 Elasticsearch ingest 节点来更加丰富我们的数据,并对这些数据做更进一步的的分析。
1277 0
使用ServiceStack.Redis实现Redis数据读写
原文:使用ServiceStack.Redis实现Redis数据读写 User.cs实体类 public class User { public string Name { get; set; ...
1460 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载