網(wǎng)(LieHuo.Net)教程 有時候在編寫存儲過程當中，比如一個插入或者修改之類的操作，要傳入大量的參數(shù)，而且還牽涉到多個主外表關(guān)系，現(xiàn)在有一種比較便利的方法可行，就是把要插入的數(shù)據(jù)以xml的形式，傳入到存儲過程中。
| 以下為引用的內(nèi)容： declare @xml varchar(8000) declare @idoc int declare @DeviceMode varchar(20) declare @EquipAttribute varchar(20) declare @Printerstyle varchar(20) declare @Head varchar(20) declare @Code varchar(20) declare @Amount varchar(20) declare @Softedition varchar(20) declare @i int --xml中是一個入庫表【CSM_B_DeviceRuKu 】和入庫明細表【CSM_B_DeviceRuKu_MX 】 set @xml='<?xml version=''1.0'' encoding=''GB2312''?> <CSM_B_DeviceRuKu BillCode=''BA1059'' EquipAttribute=''0'' Remark=''哈哈'' EnterDate=''2009-10-13'' Person=''1'' Unit=''1'' IsValid=''0'' DeviceTotal=''類型：PCI WKT2開票卡，數(shù)量：100；類型：32K大容量IC卡，數(shù)量：100；''> <CSM_B_DeviceRuKu_MX DeviceMode=''0'' EquipAttribute=''PCI WKT2開票卡'' Printerstyle='''' Softedition='''' Head='''' Code=''12313111111111'' Amount=''100'' /> <CSM_B_DeviceRuKu_MX DeviceMode=''1'' EquipAttribute=''32K大容量IC卡'' Printerstyle='''' Softedition='''' Head='''' Code=''43351212333333'' Amount=''100'' /> <CSM_B_DeviceRuKu_MX DeviceMode=''0'' EquipAttribute=''PCI WKT2開票卡'' Printerstyle='''' Softedition='''' Head='''' Code=''12313111111111'' Amount=''100'' /> <CSM_B_DeviceRuKu_MX DeviceMode=''1'' EquipAttribute=''32K大容量IC卡'' Printerstyle='''' Softedition='''' Head='''' Code=''43351212333333'' Amount=''100'' /> </CSM_B_DeviceRuKu>' --通過存儲過程sp_xml_preparedocument來解析xml EXEC sp_xml_preparedocument @idoc output, @xml --讀取xml里面的數(shù)據(jù)【獲取CSM_B_DeviceRuKu的數(shù)據(jù)】 SELECT EquipAttribute,Person,EnterDate,BillCode,Remark,Unit,IsValid,DeviceTotal FROM OpenXML(@idoc, '/CSM_B_DeviceRuKu') WITH CSM_B_DeviceRuKu -- 【獲取CSM_B_DeviceRuKu_MX的所有數(shù)據(jù)】 SELECT DeviceMode,EquipAttribute,Printerstyle,Softedition,Head,Code,Amount FROM OpenXML(@idoc,'/CSM_B_DeviceRuKu/CSM_B_DeviceRuKu_MX') WITH CSM_B_DeviceRuKu_MX set @i=1 ---游標（主要作用是循環(huán)讀取CSM_B_DeviceRuKu_MX中的數(shù)據(jù)） declare item_cursor cursor for SELECT DeviceMode,EquipAttribute,Printerstyle,Softedition,Head,Code,Amount FROM OpenXML(@idoc,'/CSM_B_DeviceRuKu/CSM_B_DeviceRuKu_MX') WITH CSM_B_DeviceRuKu_MX open item_cursor fetch next from item_cursor into @DeviceMode,@EquipAttribute,@Printerstyle,@Softedition,@Head,@Code,@Amount while @@Fetch_Status=0 begin ---只是打印出效果，現(xiàn)在@DeviceMode,@EquipAttribute,@Printerstyle,@Softedition,@Head,@Code,@Amount 都以全部獲得了值，可以對數(shù)據(jù)庫表CSM_B_DeviceRuKu_MX表進行添加和修改 print('這是第'+cast(@i as varchar(20))+'次：：：'+@DeviceMode+','+@EquipAttribute+','+@Printerstyle+','+@Softedition+','+@Head+','+@Code+','+@Amount ) set @i=@i+1 fetch next from item_cursor into @DeviceMode,@EquipAttribute,@Printerstyle,@Softedition,@Head,@Code,@Amount end close item_cursor deallocate item_cursor |
程序員人生，我編程，我富裕，記住wfuyu網(wǎng)，php教程，php學習，php手冊，CMS模版制作
聲明：本站大部分內(nèi)容是作者原創(chuàng)，少部分收集于互聯(lián)網(wǎng)供大家一起學習，原版權(quán)很多不明，如有侵權(quán)請聯(lián)系本站，謝謝！
粵ICP備14040726號-1?? 2015-2020 程序員人生 版權(quán)所有
