declare @hDoc int declare @tableName varchar(50) set @tableName ='emp' exec sp_xml_preparedocument @hDoc OUTPUT, '<newdataset> <table> <empno>x101</empno> <empname>Rohan</empname> <countorycode>1</countorycode> <statecode>123</statecode> </table> <table> <empno>x102</empno> <empname>Sohan</empname> <countorycode>1</countorycode> <statecode>456</statecode> </table> <table> <empno>x103</empno> <empname>Mohan</empname> <countorycode>2</countorycode> <statecode>789</statecode> </table> <table> <empno>x104</empno> <empname>sonam</empname> <countorycode>3</countorycode> <statecode>125</statecode> </table> </newdataset>' declare @insert varchar(50) declare @query varchar(100) set @insert=' insert into '+ @tableName + ' ' declare @select varchar(50) set @select='SELECT * FROM OPENXML(' declare @int int set @int=@hDoc declare @join varchar(50) set @join= +','+ N'''NewDataset/Table/''' + ',2) with' + @tableName set @query = @insert +@select+ convert(varchar, @int) + @join Print @query execute (@query)
insert into emp SELECT * FROM OPENXML(17,'NewDataset/Table/',2) withemp Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 17'.
declare @hDoc int declare @tableName varchar(50) set @tableName ='emp' exec sp_xml_preparedocument @hDoc OUTPUT, '<newdataset> <table> <empno>x101</empno> <empname>Rohan</empname> <countorycode>1</countorycode> <statecode>123</statecode> </table> <table> <empno>x102</empno> <empname>Sohan</empname> <countorycode>1</countorycode> <statecode>456</statecode> </table> <table> <empno>x103</empno> <empname>Mohan</empname> <countorycode>2</countorycode> <statecode>789</statecode> </table> <table> <empno>x104</empno> <empname>sonam</empname> <countorycode>3</countorycode> <statecode>125</statecode> </table> </newdataset>' insert into EMP SELECT * FROM OPENXML(@hDoc, N'/NewDataSet/Table',2) with emp
with
emp
insert into emp SELECT * FROM OPENXML(17,'NewDataset/Table/',2) withemp
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)