常常遇到需要向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。所有問題都解決了,暢快。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
