如何从SQL管理工作室获取ntext列的完整数据？

I am using SQL server 2005. In one of the tables, I have a column "xmldefinition" which is of ntext type. Now the data in this column is very huge and contains whole xml text.

我正在使用SQL Server 2005.在其中一个表中,我有一个“xmldefinition”列,它是ntext类型。现在,此列中的数据非常庞大,并包含整个xml文本。

eg:- <root><something1>....</something1></root>

I want to get the whole string from management studio and copy it outside in a xml file just to go through the whole xml manually. But when I query for this column and I copy and paste the data into another file, the contents are broken in middle and it is not complete.

我想从管理工作室获取整个字符串并将其复制到xml文件中,只是为了手动完成整个xml。但是当我查询此列并将数据复制并粘贴到另一个文件中时,内容在中间被破坏并且不完整。

eg:- <root><something1>........<somechar

I believe this will copy only some 8196 characters from xml data in column. So my question is, how do I get the complete data for this column manually. I can however write a C# code to read that column, but I want to do this manually in management studio. Any idea please.

我相信这只会从列中的xml数据中复制一些8196个字符。所以我的问题是,如何手动获取此列的完整数据。但是,我可以编写一个C#代码来读取该列,但我想在管理工作室中手动执行此操作。请问任何想法。

3 个解决方案

#1

Why not convert the data from NText to XML in your select statement? Then you get the option of opening up the XML in a separate window within SSMS.

为什么不在select语句中将数据从NText转换为XML?然后,您可以选择在SSMS中的单独窗口中打开XML。

#2

The export technique shown in SQL Server truncation and 8192 limitation worked for me. In summary it says:

SQL Server截断和8192限制中显示的导出技术对我有用。总之它说:

You can export the data to a flat file which will not be truncated. To do this:

您可以将数据导出到不会被截断的平面文件中。去做这个:

Right click the Database

右键单击数据库

Click Tasks -> Export Data

单击任务 - >导出数据

Select your Data Source (defaults should be fine)

选择您的数据源(默认值应该没问题)

Choose "Flat File Destination" for the Destination type.

为目标类型选择“平面文件目标”。

Pick a file name for the output.

选择输出的文件名。

On the "Specify Table Copy or Query", choose "Write a query to specify the data to transfer"

在“指定表复制或查询”上,选择“编写查询以指定要传输的数据”

Paste in your query

粘贴在您的查询中

Remaining steps should be self explanatory. This will output the file to text and you can open it in your favorite text editor.

其余步骤应该是自我解释的。这会将文件输出到文本,您可以在您喜欢的文本编辑器中打开它。

#3

The only way of exceeding this limit in general is via XML. For long varchar columns I normally use something like the following (the processing instruction trick avoids < being changed to < etc.)

一般来说,超过此限制的唯一方法是通过XML。对于长varchar列,我通常使用类似下面的内容(处理指令技巧避免 <被更改为<等等)< p>

select object_definition(object_id('sysdatabases')) 
 as [processing-instruction(x)] FOR XML PATH

Of course in your case the data is already XML so a simple cast should work!

当然,在您的情况下,数据已经是XML,因此简单的演员应该可以工作!

#1