Creating a JSON file from SQL Server

In this recipe, we will create a JSON file from records that we retrieve from SQL Server.

Getting ready

For this recipe, choose a table with records that you want to export to a JSON file.

How to do it...

The following steps walk you through how to create a JSON file from SQL Server records using PowerShell:

1. Open PowerShell ISE as an administrator.
2. Import the SQLPS module as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking

3. Add the following script and run it:

   $VerbosePreference = "Continue"
   
   $instanceName = "localhost"
   $databaseName = "SampleDB"
   $fileName = "C:\DATA\Customers.json"
   
   Invoke-SqlCmd -Query "SELECT * FROM SampleText" -ServerInstance $instanceName -Database $databaseName |
   ConvertTo-Json -Depth 1  |
   Out-File -FilePath $fileName
   
   $VerbosePreference = "SilentlyContinue"

4. When you are done, check the file created. The JSON file will look like this:
   

How it works...

JavaScript...