Publishing the Product Details WebService based on an Excel based Product Catalog using the SOA Suite 11g File Adapter with Synchronous File Read

On the one hand: the organization wants to publish a real Web Service, SOAP enabled and all. On the other: the data to be provided by this Web Service is maintained in Excel files and undergoes regular changes by people who are well versed in Excel but absolutely blank when it comes to Web Services.

This article demonstrates how the File Adapter can be used with a Composite application running in Oracle SOA Suite 11g to synchronously read from a file with product catalog details and return information about a single product for which the product identifier was sent to the service.

In just a few, largely declarative, steps, the bridge is built from file based article records to a real SOAP Web Service.

Based on a predefined WSDL and XSD contract, I have created a new SOA Composite application that exposes a SOAP Web Service interface with portType ShopService and operation RetrieveProductDetails.

The XSD for this operation is fairly simple:

In simple terms: the service receives a product identifier (a String) and should return a productEntry element that contains five attributes of the requested product.

The information that the composite should make available is to be read from a file. I therefore need a File Adapter binding. And because the composite takes the initiative here – and not the file system or the file adapter, the File Adapter is added as a reference, in the outbound swimlane.

The operation to configure is the Synchronous Read File:

Out of sheer laziness, I have specified a physical directory to read the file from. Note: the checkbox Delete files after successful retrieval is disabled but checked. It should be unchecked in this case, because I want to be able to read product details from the same product catalog file on multiple occasions. I will change this setting manually in the file adapter configuration file later on.

Type the name of the file that the data will be read from.

After completing the File Adapter configuration, I have added a Mediator to connect from the inbound Web Service binding to the outbound File Adapter binding:

There does not need to be a mapping for the inbound message – that contains the product identifier – as the File Adapter will simply read the entire file. However, the transformation of the file contents returned by the ScanCatalogFile service by the Mediator will use the the product identifier to filter on the requested product.

Edit the Mediator. Click on the Mapping icon to add a [new] transformation for the response.

Specify the name for the stylesheet and – very important – check the checkbox Include Request in the Reply Payload. Click OK to go to the Mapping Editor.

The mapping looks like this –

but the devil is in the details. The for-each element filters on the Product from the Catalog that has a value for the id element that is equal to the value of the productIdentifier element in the productQuery element in the original request message:

This ensures that a productEntry element is only created and returned for a single (or none at all) Product element in the Catalog.

Before I deploy the composite application, I make a small but important change, to ensure that the File Adapter will not remove the product catalog file every time it has been read:

With this change on board, the application can be deployed.

The ProductCatalog.txt file can be moved to the directory d:\temp\products.

And I can test the service.

The response comes in as expected (well, hoped for) with the details from the file:

Just for the fun of it, another test call:

With a similar response:

If I edit the ProductCatalog.txt file:

and save the changes, the new product is immediately available in the web service:

Note: For large files we probably should consider to cache the data rather than reading it from the file system all the time. Alternatively we could use a valve in the File Adapter binding (see below) to filter the file contents before returning it to the SOA Suite.

Processing an Excel based Product Catalog

The example thusfar did not touch upon Excel. So where is that part of the story? Well, I am afraid I tricked you a little bit there. The Excel part would have to be part two of this story. The image below though illustrates what would be added to what you have seen above: using the Valve (discussed in detail in https://technology.amis.nl/blog/13849/soa-suite-file-adapter-pre-and-post-processing-using-valves-and-pipelines), the File Adapter binding would be extended with functionality to turn a binary (or XLSX) Excel file into textual data. A second valve could be used to read the article id from a property in the header and filter the data read from the Excel file before returning it to the composite.

Resources

Download JDeveloper application ProductCatalog.