将SQL表输出到XML文件的最有效方法

I have server that needs to process and dump from an SQL database queries and tables into xml format on disk. This needs to be a scheduled task.

我的服务器需要处理并从SQL数据库中查询和表转储到磁盘上的xml格式。这需要是一个预定的任务。

Currently using BCP via a scheduled batch file > sql script > xp_cmdshell >bcp, but this error

目前通过预定的批处理文件> sql脚本> xp_cmdshell> bcp使用BCP，但是此错误

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Warning: Server data (172885 bytes) exceeds host-file field length (65535 bytes) for field (1). Use prefix length, termination string, or a larger host-file field size. Truncation cannot occur for BCP output files.

SQLState = S1000，NativeError = 0错误= [Microsoft] [SQL Server Native Client 10.0] [SQL Server]警告：服务器数据（172885字节）超过字段（1）的主机文件字段长度（65535字节）。使用前缀长度，终止字符串或更大的主机文件字段大小。 BCP输出文件不会发生截断。

is troubling me in the log files. I have found no solution online yet. I do not quite understand what the 'host-file field' is referring to. The original table has no column with a value as large as 172885 bytes. The output files are very large, and so far it seems as thought the data is all being written, but there seems to be some garbage at the end of all the xml files.

在日志文件中困扰我。我还没有在网上找到解决方案。我不太明白'host-file字段'是指什么。原始表没有列，其值大到172885字节。输出文件非常大，到目前为止，似乎所有数据都被写入，但似乎在所有xml文件的末尾都有一些垃圾。

Performance is important but reliability is the most important for me in this situation.

性能很重要，但在这种情况下，可靠性对我来说是最重要的。

I have tried recreating the error locally but have been unsuccessful in doing so. The server runs Windows Server 2008 r2.

我已尝试在本地重新创建错误，但这样做不成功。服务器运行Windows Server 2008 r2。

Any help or explanation/analysis of the error and it's meaning, as well as a recommendation of a simple scheduled solution to dump the sql tables/queries to xml files, would be appreciated.

任何有关错误及其含义的帮助或解释/分析，以及将sql表/查询转储到xml文件的简单计划解决方案的建议，将不胜感激。

1 个解决方案

#1

You should check out the FOR XML PATH syntax introduced in SQL Server 2005:

您应该查看SQL Server 2005中引入的FOR XML PATH语法：

SQL Server: simple example of creating XML file with T-SQL

SQL Server：使用T-SQL创建XML文件的简单示例
What's new in FOR XML in SQL Server 2005

SQL Server 2005中FOR XML的新特性

With this, you can easily create fairly nifty XML outputs, including hierarchies, attributes and more

有了这个，您可以轻松创建相当漂亮的XML输出，包括层次结构，属性等

#1