常常遇到需要向SQL Server插入批量數(shù)據(jù)，然后在存儲過程中對這些數(shù)據(jù)進行進一步處理的情況。存儲過程并沒有數(shù)組、列表之類的參數(shù)類型，使用XML類型可妥善解決這個問題。
不過，SQL Server2005對標(biāo)準(zhǔn)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默認的序列化會出現(xiàn)xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd= http://www.w3.org/2001/XMLSchema
有網(wǎng)友給出了一個完美序列化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參數(shù)
然后就可以對#temp按照普通表進行進一步處理。
我們試著執(zhí)行這個存儲過程。嗯？出錯了？！
3.原來，XML的時間標(biāo)準(zhǔn)格式是”年-月-日T時:分:秒-時區(qū)” SQL Server2005不支持時區(qū)，所以它也不能支持xml的時間格式（倒是支持年-月-日T時:分:秒）。這個問題在SQL server 2008中得到改進，完整支持了xml的時間格式。但是我們數(shù)據(jù)庫是2005，沒辦法，得想個辦法解決。解決辦法是把時間字轉(zhuǎn)成字符串，然后截取 年-月-日T時:分:秒，最后再加上東八區(qū)的時區(qū)數(shù)，這樣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參數(shù)
本地測試，成功！
4.放到服務(wù)器上測試，執(zhí)行倒是成功了，可以一查看數(shù)據(jù)，又出問題了！服務(wù)器上插入數(shù)據(jù)表的時間，和我本地測試數(shù)據(jù)庫的時間，相差8個小時！本地開發(fā)環(huán)境是windows8，服務(wù)器是windows server 2008。開發(fā)環(huán)境和服務(wù)器環(huán)境有差異，導(dǎo)致本地獲取xml帶時區(qū)，服務(wù)器不帶時區(qū)。
過于依賴環(huán)境，就太危險了！果斷放棄時間格式，修改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); }
}
}
在存儲過程中把這個時間字符串轉(zhuǎn)換成時間：
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批量寫入數(shù)據(jù)——一次有關(guān)XML時間格式的折騰經(jīng)歷
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
