Search⌘ K
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Importing Data

Explore how to import JSON and XML data into MySQL tables, using JSON data types for validation and efficient access, and LOAD XML for XML documents. Understand the requirements and methods to manage these formats within SQL.

We'll cover the following...

In SQL, data is naturally stored in a tabular form. With the two data types JSON and TEXT, MySQL allows storing data encoded in JSON or XML without enforcing a tabular format. As we may not only want to store JSON/XML in a single field using the two mentioned data types, MySQL also offers ways to import JSON/XML files directly. Before looking at these capabilities, let’s look at the two data types, JSON and TEXT, about storing JSON and XML, respectively.

Importing JSON data

JSON is MySQL’s data type that natively supports and provides efficient access to JSON documents. While we could store JSON documents as strings using the TEXT data type, JSON provides two crucial advantages over TEXT. Firstly, storing JSON documents in a column of type JSON automatically validates the input and produces an error for invalid documents:

MySQL
-- Storing the JSON document `0` as a value of type `JSON`
SELECT CAST('0' AS JSON);
    
-- Trying to store the JSON document `[` (i.e., an incomplete, empty array) as a value of type `JSON`
SELECT CAST('[' AS JSON);
...