常常遇到需要向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。所有问题都解决了，畅快。
