XML 输出在 SQL 中被截断
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2742651/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
XML Output is Truncated in SQL
提问by Muhammad Akhtar
I need to return my result set in XML and this works fine, but if the number of records are increased, my xml output is truncatedhere is my query
我需要在 XML 中返回我的结果集，这工作正常，但如果记录数增加，我的 xml 输出在truncated这里是我的查询
select t.id,t.name,t.address from test FOR XML AUTO, ROOT('Response'), ELEMENTS
However I have set some option to increase the output result set like..
但是我已经设置了一些选项来增加输出结果集，如..
Tools --> Options --> Query Results --> SQL Server --> Results to Text --> Maximum
number of characters displayed in each column
Tools --> Options --> Results --> Maximum characters per column
but still I am unable to get my desired result.
但我仍然无法得到我想要的结果。
please suggest my solution
请建议我的解决方案
EDIT: However when I click on the XML, it open in another query window and all xml shown here, but it not return all xml instead of truncated xml.
编辑：但是，当我单击 XML 时，它会在另一个查询窗口中打开并在此处显示所有 xml，但它不会返回所有 xml 而不是截断的 xml。
Thanks....
谢谢....
采纳答案by Axarydax
I've had the same problem with management studio and xml result sets. I've solved this by getting the output programatically with c#, with SqlDataReader.
我在管理工作室和 xml 结果集上遇到了同样的问题。我已经通过使用 c# 和 SqlDataReader 以编程方式获取输出解决了这个问题。
回答by user3791994
Try the following:
请尝试以下操作：
Declare @xmldata xml
set @xmldata = (select ... From test for xml...)
select @xmldata as returnXml
回答by Vin
I had the same issue. I changes my Query result setting to Unlimited, but it didnt work. There is a work around. Its not very neat, but if you do want to continue to use SQL Server Management studio and get the results. Tweak the query to convert the xml stored using:
我遇到过同样的问题。我将查询结果设置更改为无限制，但它不起作用。有一个解决办法。它不是很整洁，但是如果您确实想继续使用 SQL Server Management Studio 并获得结果。调整查询以转换使用以下方法存储的 xml：
convert(xml,'<xml><![CDATA[' + cast(MyColumnWithXml as varchar(max)) + ']]></xml>')
The complete text is returned. Please note you will have to do some formatting at you end
返回完整文本。请注意，您必须在最后进行一些格式化
回答by JohnH
This worked for me (SSMS 2012): Tools > Options > Query Results > SQL Server > Results to Grid: Maximum Characters Retried: XML data: Unlimited. Then I saved the results of the grid to a file.
这对我有用（SSMS 2012）：工具 > 选项 > 查询结果 > SQL Server > 结果到网格：重试的最大字符数：XML 数据：无限制。然后我将网格的结果保存到一个文件中。
回答by JohnH
If your XML result set is still being truncated even after changing SSMS Options, use the SQLCMD utility with :XML ON option prior to running your XML statement. Direct your output to a file using the -o switch. SQLCMD is a command line utility that is very simple to use. See http://msdn.microsoft.com/en-us/library/ms162773.aspx.
如果您的 XML 结果集在更改 SSMS 选项后仍然被截断，请在运行您的 XML 语句之前使用带有 :XML ON 选项的 SQLCMD 实用程序。使用 -o 开关将输出定向到文件。SQLCMD 是一个非常易于使用的命令行实用程序。请参阅http://msdn.microsoft.com/en-us/library/ms162773.aspx。
回答by vikscool
I know this question was asked like 10 Yearsago, but still if someone is looking for a workaround which doesn't require any changes in the DB side and is a bit similar to the Accepted answer but not with SqlDataReader.
我知道这个问题是在10年前被问到的，但是如果有人正在寻找一种不需要在 DB 端进行任何更改并且与 Accepted 的答案有点相似但与SqlDataReader.
You could make use of a DataTableand fill it using the SqlDataAdapter. Where your XMLdata will be divided into multiple rows.
您可以使用 aDataTable并使用SqlDataAdapter. 您的 XML数据将被分成多行。
For Example
例如
string xmloutput = "";
var cmd = new SqlCommand("<some sql query that returns data in xml format>", con);
var da = new SqlDataAdapter(cmd);
var dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
foreach (DataRow dr in dt.Rows)
{
xmloutput += dr[0].ToString();
}
Note:
笔记：
- the above code assumes that the only one column is returned from
DBwithXMLdata in it.
- 上面的代码假设只有一列是从
DB其中返回的XML数据。
And later on, make use of the variable xmloutput.
稍后，使用变量xmloutput。
回答by Dinesh
The XML output you get in Query output window is not really intended for saving valid XML documents. You can get valid XML out of SQL Server when you capture it as a stream using ADO or SQLXML managed classes in .NET
您在查询输出窗口中获得的 XML 输出并不是真正用于保存有效的 XML 文档。当您使用 .NET 中的 ADO 或 SQLXML 托管类将其捕获为流时，您可以从 SQL Server 中获取有效的 XML
However you can limit the query output sizes by using In SQL Server Management Studio go to Tools >> Options >> Query Results >> SQL Server >> Results to Text >> Maximum number of characters displayed in each column
但是，您可以通过使用在 SQL Server Management Studio 中转到工具 >> 选项 >> 查询结果 >> SQL Server >> 结果到文本 >> 每列中显示的最大字符数来限制查询输出大小
回答by Emil Lerch
You can use SQL Management Studio for 2008 against a SQL Server 2005 database engine. If you do that, the defaults should be large enough, but in Tools -> Options -> Query Results -> SQL Server there is a Results to Grid and Results To Text node.
您可以针对 SQL Server 2005 数据库引擎使用 SQL Management Studio for 2008。如果这样做，默认值应该足够大，但在工具 -> 选项 -> 查询结果 -> SQL Server 中有一个结果到网格和结果到文本节点。
Results to Grid allows you to control the maximum size for XML data explicitly, and the default maximum size is 2MB.
Results to Grid 允许您明确控制 XML 数据的最大大小，默认最大大小为 2MB。
If you don't have a license for SSMS 2008 you should be able to use the express edition for free here.
如果您没有 SSMS 2008 的许可证，您应该可以在此处免费使用快速版。
