SQL Resource Media Types

Media Type Overview

restSQL supports XML and JSON media types for encoding data in request and response entities (bodies), with XML being the default.

Name Mime Type Default
XML document application/xml Yes
JSON object application/json No

Request bodies are used with write operations (POST, PUT, DELETE) but not always. POST and PUT also accepts URL-encoded parameters (Content-Type: application/x-www-form-urlencoded). DELETE request bodies are optional.

Response bodies are always returned with successful execution, i.e. 200 OK. (See HTTP Status Codes for all status codes). A read operation (GET) response contains the data requested or empty set. A write operation (POST, PUT, DELETE) returns the writeResponse XML document or JSON object that contains the number of rows affected.

Media Type Control

Media types are specified in the restSQL HTTP API using HTTP Standard Headers: Content-Type and Accept. These are generally set to the same values unless URL encoded params are part of a POST or PUT, or if the application desires to submit in one encoding and receive another (unusual). If you do not explicitly set the Accept header, your framework or browser will most likely insert a default for you. For example Chrome might use something like text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8 , which means your response will be in XML format even if your Content-Type is JSON.

You may use the _output query parameter on GET requests, for example http://host/restsql/MyResource/345?_output=application/json . This overrides the Accept header value.

XML

XML request documents must conform to Request.xsd and responses to Response.xsd.

A read response is a readResponse element enclosing the data requested, as in: 
<readResponse>
   <actor actor_id="123" first_name="JULIANNE" last_name="DENCH" />
   <actor actor_id="124" first_name="SCARLETT" last_name="BENING" />
   <actor actor_id="125" first_name="ALBERT" last_name="NOLTE" />
</readResponse>
Each top-level element is the SQL Resource parent table name (or alias) with the appropriate attributes. In a hiearchical SQL Resource, the document may contain two levels of elements, as in:
<readResponse>
   <actor actor_id="123" first_name="JULIANNE" last_name="DENCH">
      <film year="2006" title="ADAPTATION HOLES" film_id="3" />
      <film year="2006" title="ATLANTIS CAUSE" film_id="43" />
   </actor>
   <actor actor_id="123" first_name="SCARLETT" last_name="BENING">
      <film year="2006" title="BERETS AGENT" film_id="67" />
      <film year="2006" title="BULL SHAWSHANK" film_id="105" />
      <film year="2006" title="CHOCOLATE DUCK" film_id="148" />
   </actor>
</readResponse>
The second level element is the child table's name (or alias) with the appropriate attributes.

If no data is found matching the query, the document will be emtpy, as in:

<readResponse>
</readResponse>

A write request is a request element followed by the data to write, as in: 

<request>
   <actor actor_id="123" first_name="JULIANNE" last_name="DENCH" />
   <actor actor_id="124" first_name="SCARLETT" last_name="BENING" />
   <actor actor_id="125" first_name="ALBERT" last_name="NOLTE" />
</request>
A write response is a writeResponse element with the number of rows affected, as in: 
<writeResponse rowsAffected="4" />

JSON

JSON objects conform to a restSQL-specific format. A read response contains an object with a single attribute of type array. The attribute is named after the parent table's plural name (or alias), as in:

{ "actors": [
      { "id": "123", "first_name": "JULIANNE", "surname": "DENCH" },
      { "id": "124", "first_name": "SCARLETT", "surname": "BENING" },
      { "id": "125, "first_name": "ALBERT", "surname": "NOLTE" }
   ]
}
Each array object in the array contains the appropriate attributes. In a hiearchical SQL Resource, the object may contain two levels of elements, as in:
{ "actors": [
      { "actor_id": "123", "first_name": "JULIANNE", "surname": "DENCH" },
         "films": [
            { "year": "2006", "title": "ADAPTATION HOLES", "film_id": "3" },
            { "year": "2006", "title": "ATLANTIS CAUSE", "film_id": "43" }
         ]
      },
      { "actor_id": "123", "first_name": "SCARLETT", "surname": "BENING" },
         "films": [
            { "year": "2006", "title": "BERETS AGENT", "film_id": "67" },
            { "year": "2006", "title": "BULL SHAWSHANK", "film_id": "105" },
            { "year": "2006", "title": "CHOCOLATE DUCK", "film_id": "138" },
         ]
      }      
   ]
}
The top-level object has an attribute with the child table's plural name (or alias) whose type is array. The child array objects contain the appropriate attributes

If no data is found matching the query, the object array will be emtpy, as in:

{ "actors": [] }

Unlike XML, the JSON write request object is encoded the same as a read response object.

A write response cotains an object with a single attribute with the rows affected, as in:

{ "rowsAffected": "4" }

More References

See the IETF RFC HTTP Standard Headers.