Software Development

Use the Power of your Database: XML and JSON

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.

1
2
3
4
5
6
7
8
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:

01
02
03
04
05
06
07
08
09
10
11
12
13
<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.

01
02
03
04
05
06
07
08
09
10
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:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[
  {
    "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.

Simon Martinelli

Simon Martinelli is a passionate Java, performance optimization and application integration expert and an active member of the Java community process (JSR-352 Java Batch and JSR-354 Money and Currency API). He is the owner of 72 Services LLC and an adjunct professor at Berne University of Applied Science in Switzerland, teaching software architecture and design and persistence technologies
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button