How to get complete data from SQL management studio for ntext column?

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.

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.

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

I believe this will copy only some 8196 characters fro开发者_StackOverflowm 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.

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

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.

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.

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.)

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!