Message output formats

With the addition of a new Web service HTTP control parameter, you can specify which output format to generate for output messages with REST-like HTTP bindings.

You can use the optional outputFormat control parameter to specify the format of the output messages from running an operation in a Web service. If you specify a value for the outputFormat parameter when you deploy a Web service, the specified output format is used every time an operation in the Web service is run. If you do not specify a value for the parameter at deployment, when an operation in the Web service is run, an output format can be specified. If an output format is not specified at run time, the default output format is used.

The HTTP binding determines the default output format, as shown in Table 1.

Table 1. Default output format for HTTP bindings
Binding	Default output format	MIME type
HTTP GET	XML	text/xml
HTTP POST (url-encoded)	XML	text/xml
HTTP POST (xml)	XML	text/xml
HTTP POST (json)	JSON	application/json

Output format exceptions

SOAP: With a SOAP binding, there is no way to influence the output format. The output format is always XML.
XSL output: If an operation has an XSL assigned to transform the output message, the outputFormat control parameter is ignored. In this case, both the format and the MIME type are determined by the XSL.

Default JSON output format

The default JSON output format follows the JSON specification at www.json.org.

Column names

Column names become JSON object names.

Data types

The same data type conversions are used for the JSON output format as are used for the XML output format.

Date/time values use the XSD date/time format and are represented as JSON strings. There are no date/time formats in JSON.
Binary values are base64–encoded JSON strings.
All numbers are mapped to JSON numbers.
SQL NULL is mapped to JSON null.
XML is embedded as JSON string.
SQL Boolean is mapped to JSON boolean.

Output formats by operation type

The following information describes the different formats that are based on the operation types.

SQL SELECT (result set)

A result set is returned as a JSON array. The following example shows a result set with information returned for multiple employees.

{"getEmployeesResponse":
    [
        {
        "EMPNO":"000010",
        "FIRSTNME":"CHRISTINE",
        "MIDINIT":"I",
        "LASTNAME":"HAAS",
        "WORKDEPT":"A00",
        "PHONENO":"3978",
        "HIREDATE":"1995-01-01Z",
        "JOB":"PRES    ",
        "EDLEVEL":18,
        "SEX":"F",
        "BIRTHDATE":"1963-08-24Z",
        "SALARY":96800.00,
        "BONUS":21113.19,
        "COMM":4220.00},
        {
        "EMPNO":"000020",
        "FIRSTNME":"MICHAEL",
        "MIDINIT":"L",
        "LASTNAME":"THOMPSON", ...}
    ]
}

If the Fetch only single row for queries check box was selected when an operation was defined, a JSON object for the first row of the query is returned instead of a JSON array. The following code shows an example of the first row of a query returned as a JSON object.

{"getEmployeesResponse":
    {
    "EMPNO":"000010",
    "FIRSTNME":"CHRISTINE",
    "MIDINIT":"I",
    "LASTNAME":"HAAS",
    "WORKDEPT":"A00",
    "PHONENO":"3978",
    "HIREDATE":"1995-01-01Z",
    "JOB":"PRES    ",
    "EDLEVEL":18,
    "SEX":"F",
    "BIRTHDATE":"1963-08-24Z",
    "SALARY":96800.00,
    "BONUS":21113.19,
    "COMM":4220.00}
}

SQL UPDATE (INSERT, DELETE, or MERGE) (update count)

The following code is an example of the JSON message format for an SQL UPDATE operation with update count.

{"updateEmployeeResponse":{"updateCount":1}}

Stored procedure call

The following code shows a JSON array for each rowset that is returned from the stored procedure call.

{"BONUS_INCREASEResponse":
    {
    "P_DEPTSWITHOUTNEWBONUSES":"",
    "P_COUNTDEPTSVIEWED":8,
    "P_COUNTDEPTSBONUSCHANGED":8,
    "P_ERRORMSG":"",
    "rowset":
        [
            {
            "WORKDEPT":"A00",
            "EMPNO":"000010",
            "BONUS":25546.95},
            {
            "WORKDEPT":"A00",
            "EMPNO":"000110",
            "BONUS":22992.12},
            {
            "WORKDEPT":"A00",
            "EMPNO":"000120",
            ...}
        ],
    "rowset2":
        [
            {
            "WORKDEPT":"A00",
            "EMPNO":"000010",
            "BONUS":25546.95},
            {
            "WORKDEPT":"A00",
            "EMPNO":"000110",
            "BONUS":22992.12},
            {
            "WORKDEPT":"A00",
            "EMPNO":"000120",
            ...}
        ],
        ...
    }
}

XQuery

The results from an XQuery are returned as a JSON array. The following example shows the XQuery results in a JSON array:

{"testXQueryResponse":
    [
        "Basic Snow Shovel, ",
        "A Deluxe Snow Shovel ...",
        ...
    ]
}

If the Fetch only single row for queries check box was selected when an operation was defined, a JSON object for the first row of the XQuery results is returned instead of a JSON array. The following code shows an example of the first string value of the XQuery results returned as a JSON object.

{"testXQueryResponse":
    "Basic Snow Shovel, "
}

Feedback