How to export a table to JSON file in SQL Server?

Recently for one of my projects, I had to export data from a table to a JSON file. To export a table to a JSON file, I have used the FOR JSON clause along with the select statement to format the retrieved data as JSON and then I ran the select statement as BCP command through XP_CMSSHELL in SSMS.

For this illustration I have used the table Application.Cities in Microsoft’s sample database WideWorldImporters. To start with, here is the select statement to fetch the required data from the Application.Cities table:

1. Simple select statement to get the required data.

SELECT 
	CityID, 
	CityName,
	LatestRecordedPopulation
FROM Application.Cities
GO
Sample data to be exported to json file

2. Convert the result set to JSON format

Now we have to format the above result set to JSON. To do this, add the FOR JSON PATH class. If you want to include the null values in the JSON result, then add INCLUDE_NULL_VALUES after a comma. On executing this select statement, you will get the result in JSON format in a single line.

/* Get table data in JSON format */
SELECT 
	CityID, 
	CityName,
	LatestRecordedPopulation
FROM Application.Cities
	FOR JSON PATH, 
		INCLUDE_NULL_VALUES
GO
Get the data from a table in JSON format

If you notice, the JSON result looks like a link. On pressing, it opens the result as a xml file on a separate window.

Get the data from a table in JSON format

3. Export the result in JSON format to a JSON file

To export the JSON formatted result to a json file, use the BCP along with XP_CMDSHELL to run the script we have created. Here is the sample script:

/* Export JSON data to a file */
DECLARE @sql varchar(1000)
SET @sql = 'bcp "SELECT CityID, CityName, ' + 
	'LatestRecordedPopulation FROM Application.Cities ' + 
	'FOR JSON PATH, INCLUDE_NULL_VALUES" ' +
	'queryout  "c:\Beaulin-Temp\cities.json" ' + 
	'-c -S MACWIN2 -d WideWorldImporters -T'
EXEC sys.XP_CMDSHELL @sql
GO

NOTE: While executing the xp_cmdshell for the first time on your server you may get an error as xp_cmdshell is disabled by default. Enable the command shell using sp_configure to proceed further.

Export the result in JSON format to a JSON file

Here is the JSON file created by BCP:

Exported data in json file

Exporting line-delimited JSON format

The file created from the above example will have the data in a single line. In case, if you want to have every JSON object in a separate line, then modify the select statement with a subquery to return the output in line-delimited format. You also need to include WITHOUT_ARRAY_WRAPPER to remove the array wrapper created in every line.

/* Get table data in line delimited JSON format */
SELECT (
	SELECT 
		CityID, 
		CityName,
		LatestRecordedPopulation
	FOR JSON PATH, 
		INCLUDE_NULL_VALUES, 
		WITHOUT_ARRAY_WRAPPER
	)
FROM Application.Cities
GO

Here is the result:

Exporting line-delimited JSON format

Then, run the select statement using BCP for exporting line delimited JSON to a file.

/* Export data formatted with line delimitation to a JSON file */
DECLARE @sql varchar(1000)
SET @sql = 'bcp "SELECT (SELECT CityID, CityName, ' +
	'LatestRecordedPopulation ' +
	'FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) ' +
	'FROM Application.Cities" ' +
	'queryout  "c:\Beaulin-Temp\cities.json" ' + 
	'-c -S MACWIN2 -d WideWorldImporters -T'
EXEC sys.XP_CMDSHELL @sql
GO
json result

Here is the JSON file created by BCP. Every row of data is in a separate line.

Exported line-delimited JSON data

Reference


Related Articles & Tools

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.