SQL Server - Export SQL database to xml files
Rédigé par Sozezzo - - Aucun commentaireSQL Script to export table data in xml files, or export schema data to xsd files
This use OLE Automation Stored Procedures (Transact-SQL), and maybe, you need to change the global configuration settings of SQL Server. It can be an issue in production environment, and this case.
The SQL Script have three stored procedures :
#spWriteStringToFile
#spExportTableToXmlFile
#spExportDatabaseToXmlFile
Check GitHub for last version : https://github.com/sozezzo/SqlServer/
You run #spExportDatabaseToXmlFile, and you can filter by the table name or the scheme name.
The filter can use wildcard character : %
Limitation :
Some column type cannot be exported. ( Ex. Type := Geography )
You can change the column type, or copy the data to a new table with transformed columns.
Suggestions
Backup and restore the database over another SQL Server, it is preferable to use develop SQL Server.
Use shared folder with low security to storage xml files.
Fix Error : SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures'
GO exec sp_configure 'show advanced options', 1 GO RECONFIGURE; GO exec sp_configure 'Ole Automation Procedures', 1 GO RECONFIGURE; GO exec sp_configure 'show advanced options', 1 GO RECONFIGURE; GO
Sql Script - Check last line to change the configuration
GO /* * Name : #spFileDetails * Description : Get info file * * Syntax : * EXEC #spFileDetails 'C:MyFile.txt'; * */ CREATE OR ALTER PROCEDURE #spFileDetails ( @Filename VARCHAR(100) /* spFileDetails 'c:autoexec.bat' */ ) AS BEGIN -- https://www.red-gate.com/simple-talk/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/ SET NOCOUNT ON; DECLARE @hr INT , --the HRESULT returned from @objFileSystem INT , --the FileSystem object @objFile INT , --the File object @ErrorObject INT , --the error object @ErrorMessage VARCHAR(255) , --the potential error message @Path VARCHAR(100) , -- @ShortPath VARCHAR(100) , @Type VARCHAR(100) , @DateCreated datetime , @DateLastAccessed datetime , @DateLastModified datetime , @Attributes INT , @size INT SET nocount ON SELECT @hr=0 , @errorMessage='opening the file system object ' EXEC @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT IF @hr=0 SELECT @errorMessage='accessing the file ''' COLLATE DATABASE_DEFAULT +@Filename+'''' , @ErrorObject=@objFileSystem IF @hr=0 EXEC @hr = sp_OAMethod @objFileSystem , 'GetFile' , @objFile out , @Filename IF @hr=0 SELECT @errorMessage='getting the attributes of ''' COLLATE DATABASE_DEFAULT +@Filename+'''' , @ErrorObject=@objFile IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'Path' , @path OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'ShortPath' , @ShortPath OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'Type' , @Type OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'DateCreated' , @DateCreated OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'DateLastAccessed' , @DateLastAccessed OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'DateLastModified' , @DateLastModified OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'Attributes' , @Attributes OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'size' , @size OUT IF @hr<>0 BEGIN DECLARE @Source VARCHAR(255) , @Description VARCHAR(255) , @Helpfile VARCHAR(255) , @HelpID INT EXECUTE sp_OAGetErrorInfo @errorObject , @source OUTPUT , @Description OUTPUT , @Helpfile OUTPUT , @HelpID OUTPUT SELECT @ErrorMessage='Error whilst ' COLLATE DATABASE_DEFAULT +@Errormessage+', ' +@Description RAISERROR (@ErrorMessage,16,1) END EXEC sp_OADestroy @objFileSystem EXEC sp_OADestroy @objFile SELECT [Path]= @Path , [ShortPath]= @ShortPath , [Type]= @Type , [DateCreated]= @DateCreated , [DateLastAccessed]= @DateLastAccessed , [DateLastModified]= @DateLastModified , [Attributes]= @Attributes , [size]= @size RETURN @hr END GO GO /* * Name : #spWriteStringToFile * Description : Write in text file * * Syntax : * EXEC #spWriteStringToFile @String = 'Hello Word!', @Path = 'C:Temp', @Filename = 'myfile.txt'; * */ CREATE OR ALTER PROCEDURE #spWriteStringToFile ( @String VARCHAR(MAX) , @Path VARCHAR(512) , @Filename VARCHAR(512) ) AS BEGIN SET NOCOUNT ON; DECLARE @objFileSystem INT , @objTextStream INT , @objErrorObject INT , @strErrorMessage VARCHAR(1000) , @Command VARCHAR(1000) , @hr INT , @fileAndPath VARCHAR(512); SET NOCOUNT ON; SELECT @strErrorMessage = 'opening the File System Object'; EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT; SELECT @FileAndPath = @path + '' COLLATE DATABASE_DEFAULT + @filename; IF @HR = 0 SELECT @objErrorObject = @objFileSystem , @strErrorMessage = 'Creating file "' COLLATE DATABASE_DEFAULT + @FileAndPath + '"' COLLATE DATABASE_DEFAULT ; IF @HR = 0 EXECUTE @hr = sp_OAMethod @objFileSystem , 'CreateTextFile' , @objTextStream OUT , @FileAndPath , 2 , True; IF @HR = 0 SELECT @objErrorObject = @objTextStream , @strErrorMessage = 'writing to the file "' COLLATE DATABASE_DEFAULT + @FileAndPath + '"' COLLATE DATABASE_DEFAULT ; IF @HR = 0 EXECUTE @hr = sp_OAMethod @objTextStream , 'Write' , NULL , @String; IF @HR = 0 SELECT @objErrorObject = @objTextStream , @strErrorMessage = 'closing the file "' COLLATE DATABASE_DEFAULT + @FileAndPath + '"' COLLATE DATABASE_DEFAULT ; IF @HR = 0 EXECUTE @hr = sp_OAMethod @objTextStream , 'Close'; IF @hr <> 0 BEGIN DECLARE @Source VARCHAR(255) , @Description VARCHAR(255) , @Helpfile VARCHAR(255) , @HelpID INT; EXECUTE sp_OAGetErrorInfo @objErrorObject , @source OUTPUT , @Description OUTPUT , @Helpfile OUTPUT , @HelpID OUTPUT; SELECT @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something' COLLATE DATABASE_DEFAULT ) + ', ' COLLATE DATABASE_DEFAULT + COALESCE(@Description, '' COLLATE DATABASE_DEFAULT ); RAISERROR(@strErrorMessage, 16, 1); END; EXECUTE sp_OADestroy @objTextStream; EXECUTE sp_OADestroy @objFileSystem; END; GO GO /* * Name : #spExportTableToXmlFile * Description : Export table in xml file * * Use stored procedure : #spWriteStringToFile * * Syntax : * EXEC #spExportTableToXmlFile @SchemaName = 'dbo', @TableName = 'User', @Path = 'C:Temp', @Filename = 'user.xml'; * */ CREATE OR ALTER PROCEDURE #spExportTableToXmlFile ( @SchemaName VARCHAR(255) , @TableName VARCHAR(255) , @Path VARCHAR(255) , @FileName VARCHAR(512) = NULL , @SafeMode INT = 0 , @CreateXSDFile INT = 0 , @Debug INT = 0 ) AS BEGIN SET NOCOUNT ON; IF (@Filename IS NULL) BEGIN SET @Filename = @SchemaName+'.' COLLATE DATABASE_DEFAULT +@TableName+'.xml' COLLATE DATABASE_DEFAULT; IF (@CreateXSDFile = 1) SET @Filename = @SchemaName+'.' COLLATE DATABASE_DEFAULT +@TableName+'.xsd' COLLATE DATABASE_DEFAULT; END -- TODO : Clean up invalided chars DECLARE @Template NVARCHAR(MAX) = ' PRINT ''-- Export Table to Xml File : [@(SchemaName)].[@(TableName)]'' PRINT ''-- '' + CONVERT(VARCHAR(50), GETDATE(), 121); DECLARE @xml varchar(max); SET @xml = (select * from [@(SchemaName)].[@(TableName)] FOR XML AUTO, ROOT (''@(SchemaName).@(TableName)'')) EXEC #spWriteStringToFile @String = @xml, @Path = ''@(Path)'', @Filename = ''@(Filename)''; ' COLLATE DATABASE_DEFAULT IF (@CreateXSDFile = 1) BEGIN SET @Template = ' PRINT ''-- Export Table to Xml File : [@(SchemaName)].[@(TableName)]'' PRINT ''-- '' + CONVERT(VARCHAR(50), GETDATE(), 121); DECLARE @xml varchar(max); SET @xml = (select TOP 0 * from [@(SchemaName)].[@(TableName)] FOR XML AUTO, ELEMENTS, XMLSCHEMA ) EXEC #spWriteStringToFile @String = @xml, @Path = ''@(Path)'', @Filename = ''@(Filename)''; ' COLLATE DATABASE_DEFAULT END SET @Template = REPLACE(@Template,'@(SchemaName)' COLLATE DATABASE_DEFAULT , @SchemaName); SET @Template = REPLACE(@Template,'@(TableName)' COLLATE DATABASE_DEFAULT , @TableName); SET @Template = REPLACE(@Template,'@(Path)' COLLATE DATABASE_DEFAULT , @Path); SET @Template = REPLACE(@Template,'@(Filename)' COLLATE DATABASE_DEFAULT , @Filename); IF (@Debug = 1) PRINT @Template IF (@SafeMode = 1) BEGIN BEGIN TRY EXEC (@Template) END TRY BEGIN CATCH PRINT '-----------------------------------' COLLATE DATABASE_DEFAULT PRINT '-- WARNING - Safe Mode' COLLATE DATABASE_DEFAULT PRINT '-- Table cannot be exported : ' COLLATE DATABASE_DEFAULT + @SchemaName +'.'+ @TableName PRINT '-----------------------------------' COLLATE DATABASE_DEFAULT END CATCH END ELSE BEGIN EXEC (@Template); END END GO GO /* * Name : #spExportDatabaseToXmlFile * Description : Export database tables in xml files * * Use stored procedure : #spExportTableToXmlFile, #spWriteStringToFile * * Syntax : * EXEC #spExportTableToXmlFile @Path = 'C:Temp'; * EXEC #spExportTableToXmlFile @Path = 'C:Temp', @TableFilter = 'Test%'; * EXEC #spExportTableToXmlFile @Path = 'C:Temp', @SchemaFilter = '%dbo%', @TableFilter = 'Test%'; * */ CREATE OR ALTER PROCEDURE #spExportDatabaseToXmlFile ( @Path VARCHAR(1024) , @SchemaFilter VARCHAR(512) = NULL , @TableFilter VARCHAR(512) = NULL , @CreateXSDFile INT = 0 , @SafeMode INT = 0 , @Debug INT = 0 ) AS BEGIN SET NOCOUNT ON; DECLARE @SchemaName VARCHAR(512); DECLARE @TableName VARCHAR(512); SELECT s.name AS SchemaName , t.name AS TableName INTO #TableToExport FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE ( @SchemaFilter IS NULL OR s.name LIKE @SchemaFilter) AND ( @TableFilter IS NULL OR t.name LIKE @TableFilter) WHILE (EXISTS (SELECT * FROM #TableToExport)) BEGIN SELECT TOP 1 @SchemaName = SchemaName, @TableName = TableName FROM #TableToExport; DELETE FROM #TableToExport WHERE @SchemaName = SchemaName AND @TableName = TableName; EXEC #spExportTableToXmlFile @SchemaName = @SchemaName , @TableName = @TableName , @Path = @Path , @CreateXSDFile = @CreateXSDFile , @SafeMode = @SafeMode , @Debug = @Debug; END END GO -- exec #spExportDatabaseToXmlFile @Path = 'C:TEMP'; -- Export data -- exec #spExportDatabaseToXmlFile @Path = 'C:TEMP', @SafeMode = 1; -- Export data & Safe mode -- exec #spExportDatabaseToXmlFile @Path = 'C:TEMP', @CreateXSDFile = 1, @Debug = 1; -- Export schema
Source :
(alternative solution with a lot steps and things to do)
https://www.easeus.com/sql-database-recovery/sql-to-xml.html