Search code examples
sql-serverssmssql-server-2017

write complete JSON to file system in SQL server

I am using: 

FOR JSON PATH

to transform my sql result set into JSON this works fine. How can I write the complete text to the file system (I am using SSMS and chose query -> results to -> results to file)? Currently, data is cutoff. I guess there is some property I can set in sql server management studio? Thanks.

Solution

  • I hope you are asking below

    As stated here

    I also use XML but a slightly different method that gets around most of the issues with XML entitisation.

    declare @VeryLongText nvarchar(max) = '';

SELECT top 100 @VeryLongText = @VeryLongText + '

' + OBJECT_DEFINITION(object_id) 
FROM sys.all_objects 
WHERE type='P' and is_ms_shipped=1

SELECT LEN(@VeryLongText)

SELECT @VeryLongText AS [processing-instruction(x)] FOR XML PATH('')

PRINT @VeryLongText /*WILL be truncated*/

    Make sure that the "XML data" limit in SSMS is set sufficiently high!

    Screenshot