如何将 SQL 查询结果保存到磁盘上的 XML 文件中 [英] How to save SQL query result to XML file on disk

问题描述

我想将表从 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:cptest.xml" -T -c -t,'

这篇关于如何将 SQL 查询结果保存到磁盘上的 XML 文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！