3

I have one table TreeVersions in SQL Server which has one JSONTree column having large tree JSON data.

Now I have run select JSONTree from dbo.TreeVersions where Tree_Id=1, I get result for tree 1. But when I copy the JSONTree column from the result, it does not get me the full result.

Can anyone tell me what query I need to get the full result or what I need to do to get the full data of this column? Thanks!

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
user3437960
  • 41
  • 2
  • 7
  • Are you copying the results from the resultset in SQL Server Management Studio? – TT. Nov 11 '16 at 07:50
  • How you are copying JSONTree column data? add some more details – Jaydip Jadhav Nov 11 '16 at 07:56
  • yes, I am copying it from resultset, by right click on it and copy – user3437960 Nov 11 '16 at 07:56
  • This has nothing to do with querying. You are asking how to copy data from an application's grid. Either switch to text view, or save the results in a file by right clicking on the top-left corner and selecting Save As. You may still get truncated data though, because it's a *grid*, not an export tool. It's job is to display the data, not export it. If the data is too big to display, it will be truncated – Panagiotis Kanavos Nov 11 '16 at 08:00
  • 1
    The proper tool for exporting data is SSIS or the Task > Export wizard (which *is* SSIS underneath) – Panagiotis Kanavos Nov 11 '16 at 08:02
  • 2
    If you are copying the result from SQL Server Management Studio, you should take into account that the length of the result will by default be limited to a certain number of characters. You can change these settings from `Query Options` (Menu > Query > Query Options; Grid > Maximum Characters retrieved). A better idea would be to follow Panagiotis' advice. – TT. Nov 11 '16 at 08:19
  • ok guys thanks for your advice, I got one solution as select JSONTree from dbo.TreeVersions where Tree_Id=136 for xml path which return me the result as link and when click on that link result opens in new tab with all data. Add For xml path after select query – user3437960 Nov 11 '16 at 08:50
  • Possible duplicate of [How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?](http://stackoverflow.com/questions/2759721/how-do-i-view-the-full-content-of-a-text-or-varcharmax-column-in-sql-server-20) – milanio Mar 28 '17 at 12:21

1 Answers1

6

Possible duplicate of How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?

The answer there : https://stackoverflow.com/a/36362262/2089232 should help you:

SELECT CAST('<![CDATA[' + LargeTextColumn + ']]>' AS XML) FROM TableName;
Community
  • 1
  • 1
milanio
  • 4,082
  • 24
  • 34
  • This works for me for outputting a large JSON result from a query. It just escapes any HTML that is within any field value but this is easily dealt with – dan richardson Mar 13 '20 at 11:01