How to save SQL query result to XML file on disk

Question

I would like to export table from SQL Server 2012 to XML file. I have found nice answer and here how to make XML result from SQL Server database query, but still I am missing how to save this result physically into file.

SQL query is:

SELECT [Created], [Text]
FROM [db304].[dbo].[SearchHistory]
FOR XML PATH('Record'), ROOT('SearchHistory')

I use Microsoft SQL Server Management Studio to execute this result. I see the XML in a result window, but I cannot save it.

There is "Save Result As.." in context menu, but with 98900 rows I run out of my 8GB memory with this option.

Is there a way how to save this query directly to the XML file on disk?

score 13 · Accepted Answer · answered Aug 06 '16 at 21:10

13

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,'

answered Aug 06 '16 at 21:10

M.Ali

67,945
13
101
127

Thanks, I can export entire table, but I cannot get work UTF-8. When I use -C65001 parameter, I get message Error = [Microsoft][SQL Server Native Client 11.0]This version of SQL Server Native Client does not support UTF-8 encoding (code page 65001). I will need to upgrade to SQL Server 2016, there is already support for UTF8 for this feature. – Tomas Kubes Aug 06 '16 at 21:28
Hello, this is just what I was looking for. Please explain what the bcp is for "EXEC xp_cmdshell 'bcp "SELECT [Created]," Thanks. – Sizons Sep 26 '17 at 06:36

score 8 · Answer 2 · answered Aug 06 '16 at 20:46

8

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

answered Aug 06 '16 at 20:46

marc_s

732,580
175
1,330
1,459

3

That is what I was looking for, but unfortunately the file is cropped, the end of the file is missing. :-( The XML is interrupted inside the element. But SSMS sais: "Query executed successfully". – Tomas Kubes Aug 06 '16 at 20:57
1

@qub1n: that's unfortunate..... in that case, I guess my only alternative would be to write a little C# program to run the query, get the results, and try to save them to disk - possibly do it in chunks (e.g. 1000 rows at a time or so) to avoid the "OutOfMemoryException" ... – marc_s Aug 06 '16 at 20:59
1

If there is a large content like a large xml file, only a part of the file is written. – XPD May 18 '20 at 16:27

score 2 · Answer 3 · answered Feb 24 '17 at 14:02

2

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.

Create an SQL query and save it to the file.
run following:

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

answered Feb 24 '17 at 14:02

Tomas Kubes

23,880
18
111
148

For me, it was necessary to add the `-y 0` option to make sure the entire XML was exported, not just the first 256 characters. – Glorfindel Feb 13 '19 at 10:45

score 2 · Answer 4 · answered Jan 19 '21 at 04:11

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,'

How to save SQL query result to XML file on disk

4 Answers4

Linked