declare @xmlDoc xml
declare @xmlDoc1 xml
---獲取要計算的xml
select @xmlDoc=c_data
from tbc_eform_data
where c_guid='@單號'
---獲取xml數(shù)據(jù)表格的數(shù)據(jù)行數(shù),此處需要根據(jù)實際情況修改xml節(jié)點名稱
declare @i varchar(5)
SELECT @i = cast(@xmlDoc.query('count(/rows/row/te123/drows/drow)') as varchar(5))

declare @tsql nvarchar(1000)
declare @xmldata varchar(1000)
while @i >0
begin
----一行數(shù)據(jù)中要判斷是否為空的數(shù)據(jù)寫進(jìn)入，需要根據(jù)實際數(shù)據(jù)修改xml節(jié)點名稱
select @tsql = 'select @xmldata = @xmlDoc.value(''(/rows/row/te123/drows/drow/c_col1)['+@i+']'',''varchar(1000)'')
+ @xmlDoc.value(''(/rows/row/te123/drows/drow/c_col2)['+@i+']'',''varchar(1000)'')
+ @xmlDoc.value(''(/rows/row/te123/drows/drow/c_col3)['+@i+']'',''varchar(1000)'')
+ @xmlDoc.value(''(/rows/row/te123/drows/drow/c_col4)['+@i+']'',''varchar(1000)'')
+ @xmlDoc.value(''(/rows/row/te123/drows/drow/c_col5)['+@i+']'',''varchar(1000)'')'
EXECUTE sp_executesql @tsql,N'@xmlDoc xml,@xmldata varchar(1000) out',@xmlDoc,@xmldata out
if @xmldata = ''
begin
----如果所有數(shù)據(jù)加起來還是空，執(zhí)行刪除操作，需要修改節(jié)點名稱
select @tsql = '
set @xmlDoc.modify(''delete /rows/row/te123/drows/drow['+@i+']'')
set @xmlDoc1 = @xmlDoc'
select @xmlDoc
EXECUTE sp_executesql @tsql,N'@xmlDoc xml,@xmlDoc1 xml out',@xmlDoc,@xmlDoc1 out 
set @xmlDoc = @xmlDoc1
end 
set @i = @i -1
end
---將轉(zhuǎn)換后的數(shù)據(jù)寫進(jìn)表單
update tbc_eform_data set c_data = @xmlDoc
where c_guid='@單號'