在sql server中导入xml数据 [英] importing xml data in sql server

问题描述

当我在sql表中插入数据时，它是静态插入的，但是一旦我应用了动态sql概念，那么在这种情况下就不会插入数据，请有人帮助我解决此问题

我的声明是...

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...

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'.

如果我这样导入，则xml数据成功导入

我的声明是

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

My Statement is

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之间缺少一个空格，因此是语法错误.注意到错误消息了吗?

If you look at the error carefully, its just a missing space between with and emp, hence the syntax error. Notice the error message?

insert into emp SELECT * FROM OPENXML(17,'NewDataset/Table/',2) withemp

这篇关于在sql server中导入xml数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！