Importing JSON data from a file and converting it into a tabular format is straightforward in SQL Server 2016. To import data from a filesystem (local disk or network location) into SQL Server, you can use the OPENROWSET (BULK) function. It simply imports the entire file contents in a single-text value.

To demonstrate this, use your knowledge from the previous section and generate content for a JSON file. Use the following query to create JSON data from the Application.People table:

USE WideWorldImporters; 
SELECT PersonID, FullName, PhoneNumber, FaxNumber, EmailAddress, LogonName, IsEmployee, IsSalesperson FROM Application.People FOR JSON AUTO;

You then save the resulting JSON text in a file named app.people.json in the C:Temp directory. Now import this JSON file into SQL Server.

By using the OPENROWSET function, the file is imported in a single-text column. Here is the code:

SELECT BulkColumn 
FROM OPENROWSET (BULK 'C:Tempapp.people.json', SINGLE_CLOB) AS x; 

The following screenshot shows the result of this import action. The entire file content is available in the single-text column named BulkColumn:

To represent a JSON file's contents in a tabular format, you can combine the OPENROWSET function with the OPENJSON function. The following code imports JSON data and displays it with the default schema (columns key, value, and type):

SELECT [key], [value], [type] 
FROM OPENROWSET (BULK 'C:Tempapp.people.json', SINGLE_CLOB) AS x 
CROSS APPLY OPENJSON(BulkColumn); 

The result is shown in the following screenshot. You can see one row for each element of a JSON array in the file:

 

Finally, this code example shows the code that can be used to import a JSON file and represent its content in tabular format, with a user-defined schema:

SELECT PersonID, FullName,PhoneNumber, FaxNumber, EmailAddress,LogonName, IsEmployee, IsSalesperson 
FROM OPENROWSET (BULK 'C:Tempapp.people.json', SINGLE_CLOB) as j 
CROSS APPLY OPENJSON(BulkColumn) 
WITH 
( 
  PersonID INT '$.PersonID', 
  FullName NVARCHAR(50) '$.FullName', 
  PhoneNumber NVARCHAR(20) '$.PhoneNumber', 
  FaxNumber NVARCHAR(20) '$.FaxNumber', 
  EmailAddress NVARCHAR(256) '$.EmailAddress', 
  LogonName NVARCHAR(50) '$.LogonName', 
  IsEmployee  BIT '$.IsEmployee', 
  IsSalesperson BIT '$.IsSalesperson' 
); 

The following screenshot shows the result of this import procedure:

As expected, the structure is identical to the one generated by the simple SELECT statement against the Application.People table.