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. |
Thank you!
We will contact you soon.
Simon MartinelliNovember 12th, 2020Last Updated: November 9th, 2020
This site uses Akismet to reduce spam. Learn how your comment data is processed.