Click here to Skip to main content
15,891,431 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
Hi,
when I am inserting data in sql table , it is inserting statically but as soon as i apply dynamic sql concept then in that case data is not inserting , please some one help me to resolve this problem

My Statement is...

SQL
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)

the OutPut I am Getting is
insert into emp SELECT * FROM OPENXML(17,'NewDataset/Table/',2) withemp
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 17'.

If I am importing like this then xml data is importing succsessfully

My Statement is


SQL
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
Posted
Updated 6-Apr-12 19:37pm
v3

1 solution

If you look at the error carefully, its just a missing space between with and emp, hence the syntax error. Notice the error message?
SQL
insert into emp SELECT * FROM OPENXML(17,'NewDataset/Table/',2) withemp
 
Share this answer
 
Comments
Member 8577904 20-Apr-12 5:59am    
Thanks for Reply but stil I am geting the Error



insert into emp SELECT * FROM OPENXML(3,'NewDataset/Table/',2) with emp
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '3'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
walterhevedeich 20-Apr-12 6:02am    
Can you post the dynamic sql statement that was produced?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900