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