Create XML File From SQL Server Using BCP


In this post we are interested in generating a well-formed XML file from the output of T-SQL code, which gets it input from a table. We already have a table in SQL Server that we want to export to an XML file.

To accomplish this we will use the bcp utility.

We have a table that has a column in it that has the type XML. The table name is [StageXMLData]. The column name is [XmlVariable]. We are going to create a new file and export the data into that file. The file will be located here: D:\xmldata.xml

Below is our SQL code.

DECLARE @sqlString VARCHAR(500) = '';
DECLARE @BCPString VARCHAR(500) = ''; 
DECLARE @DBName VARCHAR(100) = '';
DECLARE @ResultCmdShell INT = 1;  -- default to error = 1, 0 is success
DECLARE @ErrMsg VARCHAR(400) = '';
DECLARE @TempFileName VARCHAR(50) = 'xmldata.xml';
DECLARE @Directory VARCHAR(300) = 'D:\';
-- First build the string. We must fully qualify the table [beStageXmlData] in the string.
SET @sqlString = 'select XmlVariable from ' 
SET @DBName = DB_NAME();  -- get the name of this database.
SET @DBName = '[' + @DBName + ']';
SET @sqlString = @sqlString + @DBName + '.[dbo].[StageXmlData] WHERE XmlVariable IS NOT NULL';
-- The above string is made dynamic in case we change the name of this database.
		
PRINT 'xml out string: ' + @sqlString;  -- just for debugging

SET @BCPString = 'bcp "' + @sqlString + '" queryout "' + @Directory + @TempFileName + '" -S ' + @@SERVERNAME + ' -w -T';		

EXEC @ResultCmdShell = xp_cmdshell @BCPString;
IF @ResultCmdShell = 1
	BEGIN
		SET @ErrMsg = 'Error. Unable to output the xml data to the file: ' + @Directory + @TempFileName;
		RAISERROR (@ErrMsg, 16, 1);
	END
IF @ResultCmdShell = 0
	BEGIN
		SET @ErrMsg = 'Success: ' + @Directory + @TempFileName;
		RAISERROR (@ErrMsg, 10, 1);  -- 10 is not actually an error - just displays
	END

Leave a comment

Your email address will not be published. Required fields are marked *