Implementation

The composite is very, very trivial:

Composite in SOA Suite

It just maps two URL query parameters to the WHERE condition of the SQL query:

REST Service Configuration

When the service is invoked, unfortunately, the JSON returned is not really “optimal”:

{
"XxisBlanketMatkoV" : [ {
"lineId" : 278000,
"shipSiteAddress1" : "PROMATIS software GmbH",
"shipSiteAddress2" : "Pforzheimer Str. 160",
"shipSiteAddress3" : {
"@nil" : "true"
},
"shipSiteAddress4" : {
"@nil" : "true"
},
"shipSiteCountry" : "DE",
"shipSitePlz" : "76275",
"bulkFlag" : "N"
} ]
}

Issues with initial version

This is ugly for two reasons:

• One usually does not describe NULL values in JSON like that. It would be better to either “omit” the element “shipSiteAddress3” or have them set to “shipSiteAddress3”: null.
• bulkFlag is a boolean, it would be better to have this as “bulkFlag” : false.

Of course, there is an obvious solution to this: Just add a mediator or even a BPEL process and do an XSLT transformation from the data from the Database Adapter to an explicit schema, and one can map Y/N to true/false over an xsl:if and is additionally able to e.g. omit the NULL elements based on xsl:if.

However, this is “one more thing” to maintain, and if the base SELECT changes, this transformation has to be adapted as well.

Producing better “null” values

Obviously, SOA Suite first extracts the data from the database column SHIP_SITE_ADDRESS4 (which in NULL) to an xml representation of

<shipSiteAddress3 xsi:nil=”true”/> and then translates that attribute into a json child “@nil”=true.

Luckily in the meantime, SOA Suite got a patch 31926382 (which is part of SOA Bundle Patch 12.2.1.4.221122). With that patch, according to 2764402.1, one is able to modify the XSD that defines the XML elements that in the end are converted to JSON as follows:

Modified XML Schema

just add:

xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" nxsd:version="JSON" nxsd:encoding="UTF-8" nxsd:isNillableSupported="true"

and the @nil will be converted to “proper” null values.

true and false

Unfortunately, pure (Oracle) SQL does not support boolean data types in the database so far. This also makes it a bit difficult to produce true/false when fetching data through the JCA Database adapter of SOA Suite. I managed to fix this by doing the following:

1. Returning 0 for false and 1 for true in the SQL field of “bulk_flag,
2. modifying the or-mappings.xml to:

<attribute-mapping xsi:type="direct-mapping"
<attribute-name>bulkFlag</attribute-name>
<field table="XXIS_BLANKET_MATKO_V" name="BULK_FLAG" xsi:type="column"/>
<attribute-classification>java.lang.Boolean</attribute-classification>
</attribute-mapping>>

(Thus changing it from java.lang.Decimal to java.lang.Boolean.)

Summary: Prettier JSON

By implementing these two tiny changes:

• Add nxsd:isNillableSupported=”true” to schema,
• modify the or-mappings.xml to have <attribute-classification>java.lang.Boolean</attribute-classification> to

The REST service gives us:

{
"XxisBlanketMatkoV" : [ {
"lineId" : 278000,
"shipSiteAddress1" : "PROMATIS software GmbH",
"shipSiteAddress2" : "Pforzheimer Str. 160",
"shipSiteAddress3" :null,
"shipSiteAddress4" : null,
"shipSiteCountry" : "DE",
"shipSitePlz" : "76275",
"bulkFlag" : false
} ]
}

Which is way better JSON ?