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

Question

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

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

Answer 2

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!

Answer 3

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.