Migration data on SQL

標籤：

從表裡面匯出資料XML：

-- exportdeclare @xml xml set @xml = (select * from (select TableName = ‘Schema‘, xmlData = ( select * from [Schema] where id = 337 for xml auto, root(‘Schemas‘) )unionselect ‘SchemaFile‘, ( select Id, SchemaId, Data = convert(varchar(max), Data), FileName, Fullpath, RelativePath from SchemaFile where SchemaId = 337 for xml auto, root(‘SchemaFiles‘) ) ) SchemaExportfor xml auto, root(‘SchemaExports‘) )select @xml

將匯出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, @xml11       insert into @mappingExport12       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 cursor19       for select TableName, xmlData 20       from @mappingExport21 22       open exportCursor23 24       fetch from exportCursor into @tableName, @xmlData25 26       while @@fetch_status <> -127       begin28             exec sp_xml_preparedocument @idoc output, @xmlData29                   30             if (@tableName = ‘Schema‘)31             begin32                   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             end46             else if (@tableName = ‘SchemaFile‘)47             begin48                   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             end58             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 schemafile64 where schemaid = 337

 

Migration data on SQL