Migration data on SQL

将导出XML数据插入到目的表中：

  1   --   now import 
  2  
  3   declare   @idoc   int 
  4  
  5         declare   @tableName   varchar ( 255  )  
   6         declare   @xmlData   xml                
   7        
  8         declare   @mappingExport   table  (TableName  varchar ( 255  ), xmlData xml)
   9  
 10         exec  sp_xml_preparedocument  @idoc  output,  @xml 
 11         insert   into   @mappingExport 
 12         select   * 
 13         from  openxml ( @idoc ,  ‘  /SchemaExports/SchemaExport  ‘  )
  14         with  (TableName  varchar ( 255  ),
  15                xmlData  nvarchar ( max  ))
  16         exec  sp_xml_removedocument  @idoc   output 
  17        
 18         declare  exportCursor  cursor 
 19         for   select   TableName, xmlData 
  20         from   @mappingExport 
 21  
 22         open   exportCursor
  23  
 24         fetch   from  exportCursor  into   @tableName ,  @xmlData 
 25  
 26         while   @@fetch_status   <>   -  1 
 27         begin 
 28               exec  sp_xml_preparedocument  @idoc  output,  @xmlData 
 29                    
 30               if  ( @tableName   =   ‘  Schema  ‘  )
  31               begin 
 32                     print   ‘  Copying Schema  ‘  
 33  
 34                     select   * 
 35                     from  openxml ( @idoc ,  ‘  /Schemas/Schema  ‘  )
  36                     with  (Id  int  ,
  37                          TenantId  int  ,
  38                          VersionNo  int  ,
  39                          ResponseSchemaId  int  ,
  40                          SchemaName  nvarchar ( 200  ),
  41                          SOAPActino  varchar ( 200  ),
  42                          LastUpdatedUTC  datetime  ,
  43                          IsCanonical  bit  ,
  44                          DocumentType  nvarchar ( 512  ) )
  45               end 
 46               else   if  ( @tableName   =   ‘  SchemaFile  ‘  )
  47               begin 
 48                     print   ‘  Copying SchemaFile  ‘ 
 49                     select   * ,  datalength (data),  convert  (xml, data)
  50                     from  openxml ( @idoc ,  ‘  /SchemaFiles/SchemaFile  ‘  )
  51                     with  (Id  int  ,
  52                          SchemaId  int  ,
  53                          Data  varchar ( max  ),
  54                          FileName  nvarchar ( 255  ),
  55                          FullPath  nvarchar ( 255  ),
  56                          RelativePath  nvarchar ( 255  ) )
  57               end 
 58               exec  sp_xml_removedocument  @idoc  
 59               fetch   next   from  exportCursor  into   @tableName ,  @xmlData             
 60         end    
 61        
 62   select   datalength (data),  convert  (xml, data)
  63   from   schemafile
  64   where  schemaid  =   337 

Migration data on SQL

标签：

查看更多关于Migration data on SQL的详细内容...