Published by cce on 2012-03-16
With the forthcoming 2.3.1 release we will be adding an XML output and revising our default JSON format. These output formats are now designed for schema-aware applications.
Our generic formatter, for user interfaces handling arbitrary queries, is now called /:raw.
In prior versions of HTSQL, we only supported tabular output. The headers were sent first, and then correlated data was sent as lists of tuples. In this release, as we introduce nested records and segments, this structure is no longer sufficient.
With our new /:xml format, field names are used for element tags and data is represented as element content. The updated /:json format now works in a similar manner; it now uses table, link and column names as dictionary keys.
For more background information, you could read Introducing Records. We may later reintroduce a JSON tabular format (/:jsv), similar to /:csv.
The /:xml format presents query output as XML.
<?xml version="1.0" encoding="UTF-8" ?>
<htsql:result xmlns:htsql="http://htsql.org/2010/xml">
<school>
<code>art</code>
<name>School of Art & Design</name>
<campus>old</campus>
</school>
<school>
<code>bus</code>
<name>School of Business</name>
<campus>south</campus>
</school>
…
Nested records are represented as nested XML elements:
<?xml version="1.0" encoding="UTF-8" ?>
<htsql:result xmlns:htsql="http://htsql.org/2010/xml">
<department>
<school>
<code>bus</code>
<name>School of Business</name>
<campus>south</campus>
</school>
<code>acc</code>
<name>Accounting</name>
<school_code>bus</school_code>
</department>
…
When a tag cannot be determined for a field, it is named with an underscored 1-based index:
<?xml version="1.0" encoding="UTF-8" ?>
<htsql:result xmlns:htsql="http://htsql.org/2010/xml">
<school>
<name>School of Art & Design</name>
<_2>1</_2>
<_3>3</_3>
</school>
…
When a tag cannot be determined for a top-level list, a single underscore is used:
<?xml version="1.0" encoding="UTF-8" ?>
<htsql:result xmlns:htsql="http://htsql.org/2010/xml">
<_>9</_>
</htsql:result>
The /:json format presents query output as JSON.
{
"school": [
{
"code": "art",
"name": "School of Art & Design",
"campus": "old"
},
{
"code": "bus",
"name": "School of Business",
"campus": "south"
},
…
Nested records are represented as nested objects:
{
"department": [
{
"school": {
"code": "bus",
"name": "School of Business",
"campus": "south"
},
"code": "acc",
"name": "Accounting",
"school_code": "bus"
},
…
When a tag cannot be determined for a field, it is keyed using a numeric 0-based index:
{
"school": [
{
"name": "School of Art & Design",
"1": 1,
"2": 3
},
…
When the tag cannot be determined for the top-level list, a “0” key is used:
{
"0": [
9
]
…
In our JSON format, the top-level dictionary will always have a single key.
By default, tags used for XML element names or JSON keys come from the HTSQL system catalog. These are normalized SQL table and column names, or configuration overrides. Tags can also be specified using the assignment (:=) operator:
<?xml version="1.0" encoding="UTF-8" ?>
<htsql:result xmlns:htsql="http://htsql.org/2010/xml">
<school>
<name>School of Art & Design</name>
<dept_count>1</dept_count>
</school>
…
The :as decorator could be used to specify tags when its argument is an unquoted identifier.
Warning
Using :as to set tag name is not yet implemented.