3

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.

skaffman
  • 398,947
  • 96
  • 818
  • 769
Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
  • 1
    NTEXT is deprecated - don't use it anymore. If you have XML data - why not use the `XML` datatype in the first place?? That would be the most optimized storage for XML.... if you can't - then at least switch to `NVARCHAR(MAX)` instead of NTEXT...... with that, getting the full string is easy – marc_s Feb 10 '11 at 10:28
  • @marc_s - yes, you are right. But this is some kind of legacy system and we are working on a framework which adds columns as ntext only for now. That framework is from a third party vendor. So currently we have to go with ntext. – Sachin Shanbhag Feb 10 '11 at 10:30

3 Answers3

5

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.

Community
  • 1
  • 1
OldAndTired
  • 51
  • 1
  • 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.

Thomas Rushton
  • 5,746
  • 2
  • 25
  • 16
  • Absolutely works. Thanks. The surprising part is, I was not aware of this xml datatype. A simple cast works for me now... Cool. – Sachin Shanbhag Feb 10 '11 at 13:14
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.)

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!

Martin Smith
  • 438,706
  • 87
  • 741
  • 845