JSON and XML Data Handling

Introduction

Modern databases often need to store, query, and manipulate semi-structured data formats like JSON (JavaScript Object Notation) and XML (Extensible Markup Language). These formats allow flexibility in handling nested and hierarchical data. SQL databases provide built-in functions to store and work with JSON and XML efficiently.

This tutorial explores how different databases handle JSON and XML, including storage, querying, and transformation.

Why Use JSON and XML in SQL?

JSON:

• Lightweight and widely used in APIs.
• Stores nested data without needing multiple relational tables.
• Easier integration with web applications.

XML:

• Useful for structured data exchange in enterprise applications.
• Supports metadata and complex hierarchies.
• Commonly used in legacy systems and document-based storage.

Working with JSON in SQL

1. Storing JSON Data

Most modern relational databases support JSON as a native data type.

MySQL (JSON Data Type)

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100),
    order_details JSON
);

Here, order_details can store nested JSON objects like:

{
    "product": "Laptop",
    "price": 1200,
    "quantity": 2
}

PostgreSQL (JSON and JSONB Data Types)

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
);

• JSON: Stores data as a plain text string.
• JSONB: Stores data in a binary format, making queries faster.

2. Querying JSON Data

Databases provide functions to extract values from JSON fields.

Extracting JSON Values (MySQL)

SELECT customer_name, order_details->>'$.product' AS product
FROM orders;

• order_details->>'$.product' extracts the value of "product" from the JSON object.

PostgreSQL JSON Functions

SELECT details->>'name' AS product_name FROM products;

Filtering with JSON Fields

SELECT * FROM orders WHERE order_details->>'$.price' > 1000;

3. Updating JSON Data

You can modify JSON objects inside SQL tables.

Updating a JSON Field in MySQL

UPDATE orders 
SET order_details = JSON_SET(order_details, '$.price', 1400) 
WHERE id = 1;

Updating a JSONB Field in PostgreSQL

UPDATE products 
SET details = jsonb_set(details, '{price}', '1400')
WHERE id = 1;

Working with XML in SQL

1. Storing XML Data

SQL databases allow XML storage using the XML data type.

SQL Server Example

CREATE TABLE employees (
    id INT PRIMARY KEY,
    employee_data XML
);

Example XML data:

<Employee>
    <Name>John Doe</Name>
    <Department>HR</Department>
</Employee>

MySQL XML Storage (TEXT Format)

CREATE TABLE documents (
    id INT PRIMARY KEY,
    data TEXT
);

XML is stored as a TEXT field in MySQL.

2. Querying XML Data

Extracting Data from XML (SQL Server)

SELECT employee_data.value('/Employee/Name[1]', 'VARCHAR(100)') AS EmployeeName
FROM employees;

Extracting Data in MySQL (Using XPath)

SELECT ExtractValue(data, '/Employee/Department') AS Department FROM documents;

3. Updating XML Data

Modifying XML in SQL Server

UPDATE employees
SET employee_data.modify('replace value of (/Employee/Department/text())[1] with "IT"')
WHERE id = 1;

Updating XML in MySQL

UPDATE documents 
SET data = REPLACE(data, '<Department>HR</Department>', '<Department>IT</Department>')
WHERE id = 1;

JSON vs. XML in SQL: A Comparison

 Conclusion

• JSON is preferred for lightweight and flexible data exchange, especially for modern web applications.
• XML is useful for hierarchical data storage in enterprise applications.
• SQL databases provide built-in functions for querying and modifying both JSON and XML.

Choosing between JSON and XML depends on the application’s needs and database capabilities.