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

Question

我想从SQL Server 2012导出表格到XML文件。我已经找到了一个好的答案和这里讲解如何从SQL Server数据库查询生成XML结果，但我还是不知道如何将这个结果保存到文件中。

SQL查询为:

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

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

右键菜单中有“另存为结果”，但是当结果有98900行时，使用这个选项会耗尽我的8GB内存。

是否有直接将此查询保存到磁盘上的XML文件的方法？

- Tomas Kubes

Answer 1

您还可以使用SQL Server的扩展存储过程将其导出为XML文件。

但在使用之前，您需要配置SQL Server。

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

一旦在 SQL Server 中启用了 xp_cmdshell，您可以使用以下命令将数据导出到 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,'

- M.Ali

---

谢谢，我可以导出整个表，但是无法使用UTF-8。当我使用-C65001参数时，会收到错误消息：Error = [Microsoft][SQL Server Native Client 11.0]This version of SQL Server Native Client does not support UTF-8 encoding (code page 65001)。我需要升级到SQL Server 2016，因为这个版本已经支持UTF8特性。 - Tomas Kubes

你好，这正是我在寻找的。请解释一下 bcp 是什么意思：“EXEC xp_cmdshell 'bcp "SELECT [Created],"”。谢谢。 - Sizons

Answer 2

您可以始终在SSMS中使用"结果保存到文件"选项：

这将直接将查询执行的结果输出到磁盘上的文件中。

- marc_s

---

这正是我在寻找的内容，但不幸的是，文件被裁剪了，文件末尾缺失了。XML在元素内部中断了。但是SSMS显示：“查询执行成功”。 - Tomas Kubes

@qub1n：这很不幸......在这种情况下，我想我的唯一选择就是编写一个小的C#程序来运行查询，获取结果，并尝试将它们保存到磁盘上 - 可能分块进行（例如每次1000行）以避免“OutOfMemoryException”... - marc_s

如果有一个大的内容，比如一个大的XML文件，只写入文件的一部分。 - XPD

Answer 3

在 SQL Server 2012 中执行此任务很麻烦。最终我升级到了 SQL Server 2014，因为 sqlcmd 已经支持 SQL UTF-8 文件。

1. 创建一个 SQL 查询并将其保存到文件中。

2. 运行以下命令：

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

- Tomas Kubes

---

对我来说，必须添加“-y 0”选项以确保导出整个XML文件，而不仅仅是前256个字符。 - Glorfindel

Answer 4

这个例子对于大小不超过2GB的结果集对我有效。

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

- Allan F