How to save SQL query result to XML file on disk

You can also your SQL Server's extended stored procedures to export it to an xml file.

But you would need to configure the sql server before you can use it.

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

Once xp_cmdshel is enabled in the SQL Server. You can use the following command to export the data to an xml file.

EXEC xp_cmdshell 'bcp "SELECT [Created], [Text] FROM [db304].[dbo].[SearchHistory] FOR XML PATH(''Record''), ROOT(''SearchHistory'')" queryout "C:\bcptest.xml" -T -c -t,'

You can always use the "Results to File" option in SSMS:

That should output the results of the query execution directly into a file on disk

To this job in SQL Server 2012 is a pain in ass. Finally I end up to update it to SQL Server 2014 as there is already support for SQL UTF-8 files in sqlcmd.

1. Create an SQL query and save it to the file.

2. run following:

   sqlcmd -S -U sa -P sapassword -i inputquery_file_name -C65001 -o outputfile_name

This example works for me for result sets up to 2GB in size.

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

DROP TABLE IF EXISTS ##AuditLogTempTable

SELECT A.MyXML
INTO ##AuditLogTempTable
FROM
(SELECT CONVERT(nvarchar(max), 
    (
            SELECT
                A.*
            FROM
                [dbo].[AuditLog] A
                JOIN ImportProviderProcesses IPP ON IPP.ImportType = 'Z' 
                  AND A.OperatorID = IPP.OperatorID 
                  AND A.AuditTypeID in ( '400','424','425' )
            WHERE
                A.[PostTime] >= IPP.StartTime
                AND A.[PostTime] <= dateadd(second, 90, IPP.StartTime) 
                FOR XML PATH('Record'), ROOT('AuditLog')
        )
    , 0
    )   AS MyXML
) A

EXEC xp_cmdshell 'bcp "SELECT MyXML FROM ##AuditLogTempTable" queryout "D:\bcptest1.xml" -T -c -t,'