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

时间:2021-06-28 23:46:57

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


4  

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


5  

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


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 &lt; 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


4  

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


5  

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


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 &lt; 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,因此简单的演员应该可以工作!