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.