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.
Most modern relational databases support JSON as a native 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
}CREATE TABLE products (
id SERIAL PRIMARY KEY,
details JSONB
);Databases provide functions to extract values from JSON fields.
SELECT customer_name, order_details->>'$.product' AS product
FROM orders;order_details->>'$.product' extracts the value of "product" from the JSON object.SELECT details->>'name' AS product_name FROM products;SELECT * FROM orders WHERE order_details->>'$.price' > 1000;You can modify JSON objects inside SQL tables.
UPDATE orders
SET order_details = JSON_SET(order_details, '$.price', 1400)
WHERE id = 1;UPDATE products
SET details = jsonb_set(details, '{price}', '1400')
WHERE id = 1;SQL databases allow XML storage using the XML data type.
CREATE TABLE employees (
id INT PRIMARY KEY,
employee_data XML
);Example XML data:
<Employee>
<Name>John Doe</Name>
<Department>HR</Department>
</Employee>CREATE TABLE documents (
id INT PRIMARY KEY,
data TEXT
);XML is stored as a TEXT field in MySQL.
SELECT employee_data.value('/Employee/Name[1]', 'VARCHAR(100)') AS EmployeeName
FROM employees;SELECT ExtractValue(data, '/Employee/Department') AS Department FROM documents;UPDATE employees
SET employee_data.modify('replace value of (/Employee/Department/text())[1] with "IT"')
WHERE id = 1;UPDATE documents
SET data = REPLACE(data, '<Department>HR</Department>', '<Department>IT</Department>')
WHERE id = 1;| Feature | JSON | XML |
|---|---|---|
| Readability | More human-readable | Verbose and complex |
| Storage Efficiency | Compact | Larger size |
| Query Performance | Fast with JSONB (PostgreSQL) | Slower due to hierarchy |
| Use Case | APIs, logs, configurations | Document storage, enterprise apps |
Choosing between JSON and XML depends on the application’s needs and database capabilities.
Sign in to join the discussion and post comments.
Sign in