Migration data on SQL
从表里面导出数据XML:
-- export
declare @xml xml
set @xml = (select * from (
select TableName = ‘Schema‘,
xmlData = ( select * from [Schema] where id = 337 for xml auto, root(‘Schemas‘) )
union
select ‘SchemaFile‘,
( select Id, SchemaId,
Data = convert(varchar(max), Data),
FileName, Fullpath, RelativePath
from SchemaFile where SchemaId = 337 for xml auto, root(‘SchemaFiles‘) ) ) SchemaExport
for 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, @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
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。