How to invoke sql query from OSB - Proxy with transformation

This is in continuation to my last post where I have shown how to make a OSB pass through.

Using Proxy to transform: 

This is more interesting as we have some control over the data.

Lets go back to JDeveloper, where we will create a concrete WSDL file and two XSLT files each for input and output. We can create a dummy BPEL process which will create all these files.

Inside the BPEL process, use the invoke activity to invoke the DB adapter and use transform activity both before invoke and after where we pass input/output to DB adapter. This gives us 2 XSLT files and once we deploy this composite we can download the concerete WSDL. Copy these 3 files to Eclipse OSB project.

Copy the XSLT and WSDL file to OSB project

Now we are ready to work with the proxy service.

Create a new proxy service 'GetEmployeeDetails_Transform_PS', on the General tab, select WSDL Web service and browse for the WSDL we have copied from JDeveloper 'GetEmployeeDetails.wsdl'
and select GetEmployeeDetailsBPEL_pt press OK.

Now go to Message Flow tab, here we will invoke the business service and transform the input and output.

Drag and drop Pipeline pair node in the flow, this explicitly gives us request and response.
Next drag and drop stage in the request pipeline, inside stage drag and drop Service callout.

Service callout is more like a invoke activity.

Click on service callout in the flow, this will open its properties, click on browse for service and look for our business service, this is very much the same as BPEL invoke activity.

Click on OK and select the required operation from drop down in Invoking, also give name for the input and output variable for this invocation.

In BPEL terms the invoke activity is configured, next we need assign/transform before and after the invoke to pass input and receive output.

Drag and drop a Assign activity on the Request action of Service callout.

Click on the assign activity to open its properties.

Now this assign is completely different from BPEL assign activity.
This assign is combination of BPEL Assign and Transform activity.

Our task right now is to use XSLT (Transform)

Here Variable means target variable.

Expression can be either a simple XPath, XSLT, XQuery etc

Now lets click on expression and then select XSLT tab, click on browse to select required XSLT file.

Input document is the variable which we will pass as input XML to XSLT

Click on OK and mention the variable for input XML

We are done with passing the input to call the business service. Next task is to get the output and pass it to OSB response, however we cant use assign activity here.

Reason ? we are passing the response back to the body of the SOAP message, if we use assign, it will try to overwrite the whole body with the XML, we have to only change the data inside the node of body, for this we will use replace activity and we will select the option of replace node contents.

In variable: Target i.e. where will the replace happen, and the XPath tells where exactly on the In variable it will happen, . (dot) in XPath is on the same node.

In the expression go to XSLT resource browse for output XSLT, and the Input document will be $EmployeeDetailsSelect_Output, this is actually the variable which has receive data after invoking the business service (service call out).

We are done lets deploy and test it.