How to save the result of an SQL query to an XML file on disk

I would like to export a table from SQL Server 2012 to an XML file. I found a good answer , and here how to make XML the result of the SQL Server database query, but still I do not have how to physically save the output to a file.

SQL query:

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

I am using Microsoft SQL Server Management Studio to accomplish this result. I see XML in the result window, but I cannot save it.

The context menu has “Save the result as ..”, but with 98900 lines, I exit my 8 GB memory with this option.

Is there a way to save this request directly in an XML file on disk?

+4
3

SQL Server xml.

sql, .

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

xp_cmdshel SQL Server. XML .

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

" " SSMS:

enter image description here

+4

SQL Server 2012 . , SQL Server 2014, SQL UTF-8 sqlcmd.

  • SQL- .
  • :

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

0
source

Source: https://habr.com/ru/post/1650422/


All Articles