I think there is a fundamental mistake we’ve been making in using SQL. We use it both to ask a question and format the answer.

Let me give you an example by generating XML with SQL:

(Same argument can be made using JSON or any other markup language. I am using XML only as an example.)

Here is one of the gazillion ways you can currently generate XML using SQL in the Oracle Database:

SELECT XMLELEMENT (“Emp”, XMLATTRIBUTES (e.fname AS “firstName”))

FROM employees e

Below is a sample XML fragment this SQL statement may produce when executed:

<Emp firstName=”John”/>

<Emp firstName=”Mary”/>

Similarly,

SELECT XMLELEMENT(“Emp”, XMLELEMENT(“firstName”, e.fname))

FROM employees e

returns the following XML fragment:

<Emp>

<firstName>John</firstName>

</Emp>

<Emp>

<firstName>Mary</firstName>

</Emp>

Logically, the two statements ask the same question to the database (select fname from employees) and should return the same answer assuming the data is static. It is only the format of the answer that is different. However, the two SQL statements look different because the way the data should be presented is buried within the SQL statement. In other words, SQL is used not only to ask a question but also format the answer to the question.

This approach has several disadvantages. It makes SQL statements difficult to write and understand. It makes them hard to maintain, especially if changes to the format are substantial. To make matters worse, each different format requires a new SQL statement to be written, even though we might be asking the exact same question only to retrieve it in a different format.

An Alternative Approach

I am all for specifying a format for the answer while asking a question with SQL. I agree that transformation of the answer to the desired format should be as close to the silicon as possible. However, if we are going to do this, let’s do it correctly and separate the query for the data from the formatting we want for the answer.

Below are examples of statements which return the same information as above. However, these statements consist of two parts. The first part is the SQL which asks the question and the second part describes the format in which the user wants the answer to be in.

Please note that I made the syntax up. I don’t care about the specific syntax to achieve this separation at this point. All I am trying to show is that this separation is possible and very, very beneficial.

select fname

from employees

return XMLType,

rows as xmlTag “Emp”,

columns as attributes,

rename column fname to “firstName”

would result in:

<Emp firstName=”John”/>

<Emp firstName=”Mary”/>

Similarly,

select fname

from employees

return XMLType,

rows as xmlTag “Emp”,

columns as tags,

rename column fname to “firstName”

would produce

<Emp>

<firstName>John</firstName>

</Emp>

<Emp>

<firstName>Mary</firstName>

</Emp>

Using a syntax implementation like the one above, a database user can obtain data in any format it wants, be it XML or JSON. Moreover, if the syntax and its implementation is powerful, the user can even describe any arbitrary format for the answer. These output formats can be saved and reused, too.

Since the query is separate from the presentation, the SQL statement becomes much easier to write and more readable. If the format changes, the query does not change. Adding a new presentation format becomes independent from the SQL required to retrieve the data. Finally, with a sufficiently powerful formatting language implementation, developers can add a new presentation format to an SQL statement during run time as an input parameter:

select fname

from employees

return using myFormat

As I said, I am not focused on the actual syntax of the formatting language. But let’s not make a mistake about it. This is a new language. So I guess what I am saying is, SQL needs a sister to format its output. In addition to SQL, the Structured Query Language, we need a Structured Formatting Language and new database engines which can interpret both.