How to save SQL query result to XML file on disk

sql server generate xml file from query
powershell export sql query results to xml file
sql query output to file
powershell sql query output to file
create xml file from sql query
how to export data from sql server to csv file using query
sql save query results
how to use xml in sql query

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?

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

Exporting results to XML, In order to get anything other than a flat XML layout, you need to use either FOR XML SQLCLR function to save the contents of a variable (or query) into a text file. The SQL# library contains several File System functions and i try to save it as XML file on local disk using queryout (C:\temp\test.xml) i get  With the FOR XML clause, you can save the result of a SQL query as XML. It can be used both in top-level queries (SELECT statement) and in subqueries (INSERT, UPDATE, and DELETE statements). When you use a FOR XML clause, you need to specify one of the four modes: RAW, AUTO, EXPLICIT, and PATH.

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

SQL to XML, Whether you want to output the query results in SQL to XML or attempt to export the data in a SQL Server database to the XML file, you can find  I need to save xml file at local file system (a physical location on PC) which will generate after SQL query execution. However, I am able to generate xml file but don't have any idea about saving that file on PC. Refer following code which I am using for xml generation --select * from EMP for Xml RAW('Rec'),elements XSINIL,root('Data')

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

Save SQL Result in XML File on drive - MSDN, How could I save query XML result into physical file on hard drive? I am preparing multiple files having 500 records each. I tried following  Hi, How to save output of a query to a file on disk in XML format, which uses FOR XML for creating XML output and also how to include XML header in the created XML file.

Save XML in a file | Read XML from a file (in SQL Server), Here is a small hands on script that uses BCP command with QueryOut option to export data from SQL Server to file system: Output: NULL  Save a SQL query result to an XML file without SSIS or xp_cmdshell. Ask Question Asked 6 years, 7 months ago. Active 5 years, 4 months ago. Viewed 9k times

8 Ways to Export SQL Results To a Text File – SQLServerCentral, Shows results to a file in SQL Server Management Studio (SSMS) We can export the SQL Server query results to a txt file by executing the following cmdlets​: Reporting Services allows you to save reports in PDF, Excel, XML, Discuss · CPU. Memory. Disk I/O. Waits. Take a peek into our servers. One of the nice things about XML is being able to get the parent node from your current location :). Part 2: Exporting to a File. This can be done rather easily with a simple SQLCLR function to save the contents of a variable (or query) into a text file.

Output SQL data as XML and export to XML file, Hi,. Ive been able to translate my SQL data using TSQL and FOR XML PATH clause into XML format. Now I need to transport this XML result set  You can save an existing XML variable as a file from TSQL. Here is an example (I read it back in to compare)! DECLARE @testXML XML SELECT @TestXML='BloodrootSanguinaria canadensis4Mostly Shady$2.44031599ColumbineAquilegia canadensis3Mostly Shady$9.37030699Marsh MarigoldCaltha palustris4Mostly Sunny$6.81051799CowslipCaltha palustris4Mostly Shady$9.90030699Dutchman''s-BreechesDicentra

Comments
  • 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.
  • Hello, this is just what I was looking for. Please explain what the bcp is for "EXEC xp_cmdshell 'bcp "SELECT [Created]," Thanks.
  • 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".
  • @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" ...
  • If there is a large content like a large xml file, only a part of the file is written.
  • 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.