ALTER procedure pro_lhnj_UpdateEnterpriseTable
(
--@xmldoc nvarchar(4000),
@xmldoc ntext,
@tableName nvarchar(255),
@enterpriseCode nvarchar(20)
)
/*
分析xml,得到临时表
*/
as
declare @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmldoc
select * into #temp1 from openxml(@hdoc,'/form1/field',1) with
(Dname nvarchar(100),--字段名
Dvalue nvarchar(4000),--字段值
DvalueType bit --构造语句时决定是否是否加"'符号"
)
--remove
exec sp_xml_removedocument @hdoc
set @myUpdateSql = 'update '+@tableName+' set flag=1, '
declare myCursor CURSOR for(select Dname,Dvalue,DvalueType from #temp1)
open myCursor
fetch next from myCursor into @Dname,@Dvalue,@DvalueType
while (@@fetch_status <> -1)
begin
if @@fetch_status <> -2
begin
--构造语句
set @myUpdateSql = @myUpdateSql + @Dname + '='
if @DvalueType = 0
begin
set @myUpdateSql = @myUpdateSql + ''''
set @myUpdateSql = @myUpdateSql + @Dvalue
set @myUpdateSql = @myUpdateSql + ''''
end
else
begin
if @Dvalue = '' set @Dvalue = 0
set @myUpdateSql = @myUpdateSql + @Dvalue
end
set @myUpdateSql = @myUpdateSql + ','
end
fetch next from myCursor into @Dname,@Dvalue,@DvalueType
end
close myCursor
deallocate myCursor
--清楚最后一个","
set @myUpdateSql = left(@myUpdateSql,len(@myUpdateSql)-1)
--加上条件
set @myUpdateSql = @myUpdateSql + ' where Var_qycode='''+@enterpriseCode+''''
--select * from #temp1
--print @myUpdateSql
<script>
var i=0;
function doAdd()
{
i=i+1;
var nowTable=document.all.myTable;
var newTr=nowTable.insertRow(i);
var newTd=newTr.insertCell(0);
newTd.innerHTML =myTd1.innerHTML;