Unleash Database Power: Generate XML and JSON with SQL
Today databases have a lot of functionality that is often not used by software developers because they simply do not know that this exists. But knowing about these features can save a lot of time because you may write less code.
Most databases offer built-in functionalities to produce XML and JSON output directly from queries. In this guide, we’ll explore how to achieve this using Oracle Database (also applicable to PostgreSQL and SQL Server).
Generate XML & JSON with SQL
One of these hidden gems is the ability to produce XML or JSON data using a SQL SELECT statement.
In the examples, Oracle Database is used but these features are also available in other Databases like PostgreSQL or SQL Server.
Sample Data Model: Employees with Phones
Let’s assume we have the following model and we want to have employees with their phones.
Generating XML from Database Data
In Oracle Database producing XML would look like this.
select xmlelement(
name "employees",
xmlagg(xmlelement(name "employee",
xmlattributes(EMPLOYEE.ID, EMPLOYEE.NAME),
xmlelement(name "phones", (select xmlagg(xmlelement(name "phone",
xmlattributes(PHONE.PHONENUMBER, PHONE.TYPE)))
from PHONE
where PHONE.EMPLOYEE_ID = EMPLOYEE.ID)))))
from "EMPLOYEE"
The result of the query will be this XML:
<employees>
<employee ID="1" NAME="Ursula Friedman">
<phones>
<phone PHONENUMBER="031 333 11 12" TYPE="WORK"/>
</phones>
</employee>
<employee ID="2" NAME="Hans Boss">
<phones>
<phone PHONENUMBER="031 333 11 01" TYPE="HOME"/>
<phone PHONENUMBER="032 311 43 12" TYPE="WORK"/>
</phones>
</employee>
</employees>
Creating JSON Output with SQL
For sure this also works for JSON in Oracle Database.
SELECT
json_arrayagg("employee".employee)
FROM (SELECT
json_object(
KEY 'id' value EMPLOYEE.ID,
KEY 'name' value EMPLOYEE.NAME,
KEY 'phones' value json_arrayagg(json_object(KEY 'number' value PHONE.PHONENUMBER, KEY 'type' value PHONE.TYPE))
) employee
FROM EMPLOYEE JOIN PHONE ON PHONE.EMPLOYEE_ID = EMPLOYEE.ID
GROUP BY EMPLOYEE.ID, EMPLOYEE.NAME) "employee"
The query produces this result:
[
{
"id": 1,
"name": "Ursula Friedman",
"phones": [
{
"number": "031 333 11 12",
"type": "WORK"
}
]
},
{
"id": 2,
"name": "Hans Boss",
"phones": [
{
"number": "031 333 11 01",
"type": "HOME"
},
{
"number": "032 311 43 12",
"type": "WORK"
}
]
}
]
Conclusion
By leveraging the built-in functionalities of your database to generate XML and JSON data directly from SQL queries, you can significantly improve development efficiency and streamline data exchange processes.
Start reading the manual of your database today. Have fun!
Related Posts:
I’m fighting for simplicity in software development.