sqlserver中访问xml
有时候在编写存储过程当中,比如一个插入或者修改之类的操作,要传入大量的参数,而且还牵涉到多个主外表关系,现在有一种比较便利的方法可行,就是把要插入的数据以xml的形式,传入到存储过程中。
![](/Images/OutliningIndicators/ContractedBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
Code
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开票卡,数量:100;类型:32K大容量IC卡,数量: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'' />
![](https://images.cnblogs.com/dot.gif)
![](https://images.cnblogs.com/dot.gif)
![](https://images.cnblogs.com/dot.gif)
![](https://images.cnblogs.com/dot.gif)
![](https://images.cnblogs.com/dot.gif)
![](https://images.cnblogs.com/dot.gif)
![](https://images.cnblogs.com/dot.gif)
![](https://images.cnblogs.com/dot.gif)
</CSM_B_DeviceRuKu>'
--通过存储过程sp_xml_preparedocument来解析xml
EXEC sp_xml_preparedocument @idoc output, @xml
--读取xml里面的数据【获取CSM_B_DeviceRuKu的数据】
SELECT EquipAttribute,Person,EnterDate,BillCode,Remark,Unit,IsValid,DeviceTotal
FROM OpenXML(@idoc, '/CSM_B_DeviceRuKu')
WITH CSM_B_DeviceRuKu
-- 【获取CSM_B_DeviceRuKu_MX的所有数据】
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
---游标(主要作用是循环读取CSM_B_DeviceRuKu_MX中的数据)
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
---只是打印出效果,现在@DeviceMode,@EquipAttribute,@Printerstyle,@Softedition,@Head,@Code,@Amount 都以全部获得了值,可以对数据库表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