--这个存储过程的目的是将XML填写入指定的表中
但是实际运行时总提示@hDoc未声明
经过调试,发现是第三步时exec出错,如果不动态拼写SQL,直接写insert 语句就没有问题请教各位,此处该如何写?CREATE Procedure procInsertTable
@insertxml ntext ,
@tablename varchar(100)
AS
declare @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @insertxml
exec(N' insert into '+ @tablename+' select * '
+' from openxml(@hDoc,''/DocumentElement/DataTable'',2)'
+' with #aaa ')
GO
但是实际运行时总提示@hDoc未声明
经过调试,发现是第三步时exec出错,如果不动态拼写SQL,直接写insert 语句就没有问题请教各位,此处该如何写?CREATE Procedure procInsertTable
@insertxml ntext ,
@tablename varchar(100)
AS
declare @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @insertxml
exec(N' insert into '+ @tablename+' select * '
+' from openxml(@hDoc,''/DocumentElement/DataTable'',2)'
+' with #aaa ')
GO
@insertxml ntext ,
@tablename varchar(100)
AS
declare @hDoc int
declare @sql nvarchar(2000)
EXEC sp_xml_preparedocument @hDoc OUTPUT, @insertxml
set @sql=N' insert into '+ @tablename+' select * '
+' from openxml('+rtrim(@hDoc)+',''/DocumentElement/DataTable'',2)'
+' with #aaa '
exec(@sql)
GO
@insertxml ntext ,
@tablename varchar(100)
AS exec(N'
declare @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, '+@insertxml+'
insert into '+ @tablename+' select * '
+' from openxml(@hDoc,''/DocumentElement/DataTable'',2)'
+' with #aaa ')
<DocumentElement>
<Table>
<FCc>2008</FCc>
<FMailID>00153AE4-2848-4F69-A9B2-D1451BE71C9F</FMailID>
</Table>
</DocumentElement>
@insertxml ntext ,
@tablename varchar(100)
AS
declare @hDoc int ,@sql varchar(4000)
EXEC sp_xml_preparedocument @hDoc OUTPUT, @insertxml
set @sql=N' insert into '+ @tablename+' select * '
+' from openxml('+rtrim(@hDoc)',''/DocumentElement/DataTable'',2)'
+' with #aaa '
exec(@sql)
set @tablename='ta'
set @hDoc=1
set @sql=N' insert into '+ @tablename+' select * '
+' from openxml('+rtrim(@hDoc)+',''/DocumentElement/DataTable'',2)'--少了一个+号
+' with #aaa '
print @sql--查看一下是不是要得到的效果
现在的方案是改为下面的,可以了
exec(N'
declare @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, '''+@insertxml+'''
insert into '+ @tablename+' select * '
+' from openxml(@hDoc,''/DocumentElement/DataTable'',2)'
+' with '+ @tablename+' #aaa ')