Use the Power of your Database: XML and JSON

Simon MartinelliNovember 12th, 2020Last Updated: November 9th, 2020

0 53 2 minutes read

Today databases have a lot of functionality that is often not used by software developers because they simply not know that this exists.
But knowing about this features can save a lot of time because you may write less code.

One of this hidden gems is the ability of producing 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.

Model

Let’s assume we have the following model and we want to have employees with their phones.

XML

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>

JSON

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

Knowing the features of your database can save you time. Start reading the manual of your database today. Have fun!

Published on Java Code Geeks with permission by Simon Martinelli, partner at our JCG program. See the original article here: Use the Power of your Database: XML and JSON

Opinions expressed by Java Code Geeks contributors are their own.