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