Web services and XSLT: an IBM Data Studio example
a Web service and apply an XSLT transformation to the XML output, perform the
Build a piece
of business logic that you want to expose as a Web service. For this example,
build an SQL statement, called GetEmp, which returns details for a
specific employee by joining columns from the EMPLOYEE, EMP_PHOTO, and EMP_RESUME
The following SQL statement takes EMPNO as an input parameter and returns
details for a specific employee by joining data from the three aforementioned
tables that are part of the SAMPLE database:
(SELECT resume FROM emp_resume
WHERE emp_resume.empno = :empno AND
emp_resume.resume_format = 'ascii') AS resume,
(SELECT picture FROM emp_photo
WHERE emp_photo.empno = :empno AND
emp_photo.photo_format = 'gif') AS picture
WHERE employee.empno = :empno
Note: If you followed along in this series, youve likely
created the SAMPLE database thats provided with a DB2 installation; however,
not all parts in this series use this database. If you dont have the SAMPLE
database, you can create it by entering the db2sampl command in your
operating system command prompt. The EMPLOYEE EMP_PHOTO, and EMP_RESUME tables schemas looks like this:
I used the Overview Diagram feature in IBM Data Studio (introduced in Part 3:
Overview diagrams - The Basics and Part 4
Overview diagrams The Rest of the Story). You can see that IBM Data Studio automatically detected the business
relationship between the EMPLOYEE and DEPARTMENT tables, and gives you details
as to the underlying columns of these tables. You can see from the previous
figure and SQL statement that this SQL statement returns relational data, as
well as data from a character large object (CLOB) column, and a binary large
object (BLOB) column. This varying data types will present a challenge (as you
will see) for the XML output of our Web service, and XSLT can be used to solve
Save this SQL statement as GetEmp such that the
Data Project Explorer looks like this:
In the middle of the previous figure, you can see that the
project from which you launched the New Web Service window becomes the default
project (as shown in the Project field) for the Web service. You can use
the Project drop-down list to select a different project, or even create
a new project for this Web service by clicking New.
If you need help with this step, or the next two steps,
refer to Part
11: Transforming Business Logic into Web services.
the target application server is started and build the Web service such that it
can be invoked using the REST protocol, as follows:
Locate the URL
of the Web service by launching the Web Service Explorer and getting the URL of
the Web Services Description Language (WSDL) document (the highlighted portion
Locate the URL
to invoke your Web service using the GET
method of a RESTful invocation (and copy the highlighted text within the quotes
in the following figure):
Paste the URL
from the previous figure into your Web browsers address field and append the
name of the Web service (getEmp) and a value of 000130 for the EMPNO parameter such that the URL looks
like this: http://localhost:8080/DatabaseJournalProjectSOA_FEMALEPERSONNEL/rest/HRTOOL/getEmp?empno=000130.
Leave this Web browser
session open for the remainder of this article.
The output in your Web browser should look like:
You can see that the Web service was properly executed.
Note that it returns the data for employee 000130 from the EMPLOYEE table:
The output of the RESUME CLOB column in the EMP_RESUME
table is properly displayed (since it is character data):
However, notice that the output of the PICTURE BLOB column
in the EMP_PHOTO table isnt properly
Obviously, this isnt the way you want to display your
data in a Human Resources application, but we know we have the data; its just
a matter of formatting it, and that is where XSLT comes into play.
XSLT transform for the XML-based output message of the Web service as follows:
Select the Web
service you created, right-click, and select Manage XSLT:
XSL Transformations window opens. Click the Browse button associated
with the Transformation of Output Messages box and ensure the XSL
file field displays the location of this getEmp_HTML_Response.xsl file, as shown below:
You can see in the previous figure that you can transform
any input XML message using the Transformation of Input Messages field.
If you want to clear a transformation for an input or
output XML message, simply click the corresponding Reset the select file button
The Generate Default button is used to generate a default
XML schema for the input messages and the output messages. For example, if you
click this button, IBM Data Studio will generate the following XSD document:
<?xml version="1.0" encoding="UTF-8"?>
xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:example" xmlns:tns="urn:example">
<element name="empno" type="xsd:string" nillable="true"/>
<element maxOccurs="unbounded" minOccurs="0" name="row">
<element name="EMPNO" type="xsd:string"/>
<element name="FIRSTNME" type="xsd:string"/>
<element name="MIDINIT" type="xsd:string" nillable="true"/>
<element name="LASTNAME" type="xsd:string"/>
<element name="WORKDEPT" type="xsd:string" nillable="true"/>
<element name="PHONENO" type="xsd:string" nillable="true"/>
<element name="HIREDATE" type="xsd:date" nillable="true"/>
<element name="JOB" type="xsd:string" nillable="true"/>
<element name="EDLEVEL" type="xsd:short"/>
<element name="SEX" type="xsd:string" nillable="true"/>
<element name="BIRTHDATE" type="xsd:date" nillable="true"/>
<element name="SALARY" type="xsd:decimal" nillable="true"/>
<element name="BONUS" type="xsd:decimal" nillable="true"/>
<element name="COMM" type="xsd:decimal" nillable="true"/>
<element name="RESUME" type="xsd:string" nillable="true"/>
<element name="PICTURE" type="xsd:base64Binary" nillable="true"/>
Web service using the same steps and options as you did in Step 3.
Return to the
Web browser session where you invoked your Web service in Step 6 and press Enter.
This action will once again invoke the same Web service and pass it EMPNO=000130 as the input parameter.
Your Web browser window should now look like this:
You can see that your Web service now takes the XML and
transforms it into HTML, which is shown in the previous figure: quite a
Note: When I tested the steps in this article with Microsoft Internet
Explorer (IE) as the Web browser, I noticed that the picture (BLOB) column did
not render correctly. Im currently investigating this nuance.
Wrapping it all up
article, I showed you how to further extend the point-and-click framework for
building Web services provided by IBM
Data Studio by applying an XSLT transformation to the output XML message
generated by the Web service. Specifically, I showed you how to take the output
of a Web service and convert it to HTML for display in a Web browser.
author would like to acknowledge the help and teachings of Michael Pauser, and
IBM developer in the Silicon
Valley lab that is
the true mastermind behind the simplicity of IBM Data Web Services.)
See All Articles by Columnist Paul C. Zikopoulos
IBM and DB2 are trademarks or registered trademarks
of International Business Machines Corporation in the United States, other countries, or both.
is a trademark of Microsoft Corporation in the United States, other countries, or both.
Linux is a
registered trademark of Linus Torvalds in the United States, other countries, or both.
company, product, or service names may be trademarks or service marks of
International Business Machines Corporation, 2009.
solutions, and advice in this article are from the authors experiences and are
not intended to represent official communication from IBM or an endorsement of
any products listed within. In addition, the code sample is provided for
illustrative purposes only and there is no express guarantee of quality of
operation. Neither the author nor IBM is liable for any of the contents or
downloads in this article. The accuracy of the information in this article is
based on the authors knowledge at the time of writing.
Any references in this information to non-IBM Web sites are provided
for convenience only and do not in any manner serve as an endorsement of those
Web sites. The
materials at those Web sites are not part of the materials for this IBM product and use of
those Web sites is at your own risk.