Below is a script that will allow you, as the title suggests, to export a table as an XML file.
By use of the find and replace function in your preferred text editor, or by way of the text editor in SSMS, input your values for the required fields, i.e. replace TableName with the name of the table you will be working off.
SERVER NAME: ServerName
DATABASE NAME: DatabaseName
SCHEMA NAME: SchemaName
TABLE NAME: TableName
SAVE LOCATION ADDRESS: AddressName
FILE NAME: FileName (Excluding .xml extension)
Then run the script below and the table will be exported as an XML file to the location and file name of your choosing.
/* FIND AND REPLACE: SERVER NAME: ServerName DATABASE NAME: DatabaseName SCHEMA NAME: SchemaName TABLE NAME: TableName SAVE LOCATION ADDRESS: AddressName FILE NAME: FileName */ -- Run on ServerName USE DatabaseName; GO DECLARE @saveLocation AS VARCHAR(510) DECLARE @nameOfFile AS VARCHAR(255) DECLARE @instanceName AS VARCHAR(255) DECLARE @cmd NVARCHAR(1020) SET @saveLocation = 'AddressName' SET @nameOfFile = 'FileName' SET @saveLocation = @saveLocation + '\' + @nameOfFile + '.xml' SET @instanceName = ( SELECT @@servername ) -- Please note that the fully qualified table name is needed SELECT @cmd = ' bcp "SELECT * FROM [DatabaseName].[SchemaName].[TableName] row for xml auto, root(''rows''), elements" ' + 'queryout "' + @saveLocation + '" -S ' + @instanceName + ' -T -w -r -t'; EXEC xp_cmdshell @cmd; GO
This script is enabled by two utilities xp_cmdshell and BCP.
For an explanation of each please view the associated links.
Tidbytez 