如何将 SQL 查询结果保存到磁盘上的 XML 文件

本文介绍了如何将 SQL 查询结果保存到磁盘上的 XML 文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想将表从 SQL Server 2012 导出到 XML 文件.我找到了不错的答案和这里如何从 SQL Server 数据库查询中生成 XML 结果，但我仍然缺少如何将此结果物理保存到文件中.

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 查询是:

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

我使用 Microsoft SQL Server Management Studio 来执行此结果.我在结果窗口中看到 XML，但无法保存.

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

上下文菜单中有结果另存为.."，但有 98900 行，我用这个选项用完了我的 8GB 内存.

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

有没有办法将这个查询直接保存到磁盘上的 XML 文件中?

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

推荐答案

您还可以将 SQL Server 的扩展存储过程导出为 xml 文件.

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

但是您需要先配置 sql server 才能使用它.

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

一旦在 SQL Server 中启用了 xp_cmdshel.您可以使用以下命令将数据导出到 xml 文件.

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