Loading JSON data from our Star Wars example API

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Some other pages relevant to these blogs include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Using JSON in SQL Server, including the new SQL 2025 functions Part two of an eight-part series of blogs
SQL Server 2025 introduces a new JSON data type and some new JSON functions - we thought we'd use this as an excuse to create this masterclass on working with JSON in SQL Server JSON, SQL Server and the example for this series of blogs Loading JSON data from our Star Wars example API (this blog) Storing JSON in a table using the new SQL Server 2025 json data type Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY) Extracting data from JSON - part 2 (using OPENJSON) Converting JSON data into a SQL Server table Joining T-SQL and JSON data Using the new preview modify keyword to change JSON

Using JSON in SQL Server, including the new SQL 2025 functions

Part two of an eight-part series of blogs

SQL Server 2025 introduces a new JSON data type and some new JSON functions - we thought we'd use this as an excuse to create this masterclass on working with JSON in SQL Server

JSON, SQL Server and the example for this series of blogs
Loading JSON data from our Star Wars example API (this blog)
Storing JSON in a table using the new SQL Server 2025 json data type
Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY)
Extracting data from JSON - part 2 (using OPENJSON)
Converting JSON data into a SQL Server table
Joining T-SQL and JSON data
Using the new preview modify keyword to change JSON

Posted by Andy Brown on 27 November 2025

In this blog

Getting the JSON data
If you get file access problems

I've chosen to base this series of blogs on the list of films, characters, species, planets, starships, etc made publicly available through the excellent Star Wars API:

Unlike most APIs, this one doesn't require payment or sign-up.

Getting the JSON data

Start by getting (for example) a list of the characters appearing in the Star Wars films:

Go to the people subfolder, then copy the JSON text returned into a new text file.

You should now have a file like this:

I downloaded the films too, and created these two files in the c:\wiseowl folder.

You can now use SQL like this to read the text in from your file into a single row and single column table, then access the value of this single value:

-- create a variable to hold the JSON text

DECLARE @json_text NVARCHAR(MAX)

-- set this variable to be the contents of the file

SELECT @json_text = j.BulkColumn

FROM OPENROWSET(

BULK 'C:\wiseowl\person.json',

SINGLE_CLOB

) as j;

-- show this worked

print(@json_text)

This should give you something like this:

The start of the text in the variable created by the above SQL.

I haven't explained much about the SQL above because it's not really to do with JSON - this will be a long enough series of blogs as it is!

If you get file access problems

If you find that SQL doesn't allow you to read the file, first make sure that you're storing the file on the same drive as your copy of SQL Server. You should then find out what service account you're using by running a query like this:

-- list the service account used

SELECT servicename, service_account

FROM sys.dm_server_services

WHERE servicename LIKE 'SQL Server (%';

For my laptop this gave this:

I'm using service account NT Service\MSSQL$SQL2025.

You can now right-click on the folder containing your JSON text files and choose to change its properties. Click on the Security tab, then choose to add a group or user name:

From the dialog box which appears, you should then click on Locations...

Make sure that you're looking on the correct computer in the dialog box which appears, then select OK and paste in the user account you want to add:

Here I'm adding the SQL Server 2025 instance's service account to the access rights for this folder.

This should sort out any access issues you have.

Now that you have JSON in a text variable, let's look at how you can store it in a table.

Parts of this blog
JSON, SQL Server and the example for this series of blogs Loading JSON data from our Star Wars example API (this blog) Storing JSON in a table using the new SQL Server 2025 json data type Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY) Extracting data from JSON - part 2 (using OPENJSON) Converting JSON data into a SQL Server table Joining T-SQL and JSON data Using the new preview modify keyword to change JSON

Parts of this blog

JSON, SQL Server and the example for this series of blogs
Loading JSON data from our Star Wars example API (this blog)
Storing JSON in a table using the new SQL Server 2025 json data type
Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY)
Extracting data from JSON - part 2 (using OPENJSON)
Converting JSON data into a SQL Server table
Joining T-SQL and JSON data
Using the new preview modify keyword to change JSON

Some other pages relevant to these blogs include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This blog has 0 threads

Add a new post

ALL BLOGS

SQL BLOGS

SQL BLOGS

Getting the JSON data

If you get file access problems

Head office

London

Manchester