使用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 VisitTimeinto #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 VisitTimeinto #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 VisitTimeinto #temp from @strxml.nodes('//Model') T(c)

Ok。所有问题都解决了，畅快。