Using industry standard data formats with WebSphere ESB and DB2 Version 9 pureXML

Within the context of service orientation, the use of standardized message formats is receiving increased attention. Message formats have been standardized for many industries, including insurance, financial trading, the federal government, and others. Using the healthcare industry and the Health Level 7 (HL7) standard as examples, this article presents a scenario that shows how using WebSphere® Enterprise Service Bus and DB2® Version 9 pureXML can help a pharmaceutical company satisfy legal obligations for exposing standardized data about its products. The approach presented can be applied to all other XML-based industry-specific standards. This content is part of the IBM WebSphere Developer Technical Journal.

Mohab El-Hilaly (, Software Engineer, IBM

Mohab El-Hilaly works as a Software Engineer at IBM Cairo Technology Development Center, Egypt, where he develops and designs custom solutions using Websphere Enterprise Service Bus technology for various customers. Prior to that he developed custom solutions using various Microsoft Technologies, mainly ASP.NET. He obtained a bachelors degree in Computer Science from the American University in Cairo. In his spare time he enjoys watching soccer and playing console games

Andre Tost, Senior Technical Staff Member, IBM

Andre Tost works as a Senior Technical Staff Member in the Software Group's Enterprise Integration Solutions organization, where he helps IBM's customers establishing Service-Oriented Architectures. His special focus is on Web services technology. Before his current assignment, he spent ten years in various partner enablement, development and architecture roles in IBM software development, most recently for the WebSphere Business Development group. Originally from Germany, he now lives and works in Rochester, Minnesota. In his spare time, he likes to spend time with his family and play and watch soccer whenever possible.

Alaa Youssef (, IT Architect, IBM

Alaa Youssef works as an IT Architect at IBM Cairo Technology Development Center, Egypt, where he helps IBM's customers in devising SOA solutions. From 1998 till mid 2005, he worked as a Research Staff Member at IBM T.J. Watson Research Center, New York. He received the PhD degree in computer science in 1998 from Old Dominion University, Virginia, USA. He received the BS and MS degrees in computer science from Alexandria University, Egypt, in 1991 and 1994, respectively. He has authored several papers, and patents.

13 June 2007


Many industries strive for a higher degree of standardization for facilitating interaction and data exchange between companies, as well as with their external business partners. To achieve this, industries have developed standards specific to their businesses that enable consistency of information across all parties, and ensure compliance with any applicable regulatory laws. For example, industry standard data formats include ACORD for insurance, FpML for financial derivatives, FIXML for financial trading, MISMO for mortgages, and GJXDM (and others) for federal government applications. The pharmaceutical industry is also engaged in several standardization efforts, one of which is the Health Level 7 (HL7) standard. HL7 targets several areas and scenarios, all represented as separate artifacts within the overall effort. One example of this is the Structured Product Labeling standard, which is the standard used as the example in this article.

The business scenario

In the United States, every prescription drug must be approved by the Food and Drug Administration (FDA). As part of that process, detailed information about the drug must be made available to the FDA and all potential distributors, such as pharmacies, hospitals, and so on. The exact information that must be offered is described in a standard XML schema called HL7 Structured Product Labeling (SPL), which is part of the HL7 standard.

Initially described as a PDF document, the current version of the standard defines an XML schema for the complete description of a drug, including information such as brand name, generic name, dosage form, active and inactive ingredients, and so on. The information included is primarily used by a pharmaceutical company to obtain approval for a prescription drug by the FDA, which mandates that the information be made available in the standard format. The information is later made publicly available through registries, which differ from country to country. For the US, SPL information can be obtained from the National Library of Medicine. Hence, aside from being a useful tool to facilitate easy exchange of information, complying with this standard is legally mandated, making an even bigger case for its widespread support.

This article describes how a fictional pharmaceutical company sets up a Web service that is accessible by its clients (and the FDA) to obtain SPL documents for its drugs. The actual SPL-compliant data is stored in a DB2 Version 9 pureXML database. The Web service is exposed via an Enterprise Service Bus (ESB) that is implemented using the WebSphere ESB product. The integration of these two products can help provide a solution for exposing SPL data over a standard Web services interface.

While the example in this article is based on a healthcare industry standard, the same approach is applicable to all the other XML-based industry specific standards.

Architecting the solution using an Enterprise Service Bus

An ESB exposes virtual service interfaces to consumers within and outside of a company's internal enterprise network. The interfaces are virtual because they offer access to data and functionality to a service requester that does not match the actual service provider, either in terms of protocol, location, or data format. The ESB maps a service provider's offered data format to the requester by either transforming it directly (for example, using an XSLT style sheet), or by augmenting it with data retrieved from another data source. In cases where existing functionality or data must be made available to a new set of consumers -- possibly requiring support for a standard -- the ESB can handle the necessary processing without affecting the existing application.

In this fictional scenario, a pharmaceutical company wants to expose information about its drugs in the standardized SPL format, described earlier. This company uses DB2 Version 9 pureXML to store the actual data, taking advantage of the product's built-in support for handling of XML data. The information is made accessible via an ESB, which receives requests for SPL information and forwards them to an internal service that does not deal with the information itself, but returns technical data that enables the ESB to retrieve SPL-compliant documents from the database. Figure 1 shows what the architecture for the solution might look like.

Figure 1. ESB architecture
Figure 1. ESB architecture

In Figure 1, the ESB uses an internal service provider, as well as a Web service interface, on top of DB2 pureXML to retrieve the SPL information, which is then returned to the requester as an XML document. Alternatively, the ESB could expose the XQuery capabilities of the database directly as a Web service, without using any other internal service provider. How exactly the internal services are leveraged is completely transparent to the requester, since it only uses the virtual service interface offered by the ESB.

The remainder of this article describes the building of this type of solution, in which the mediation running inside WebSphere ESB explicitly invokes the XQuery functionality offered by DB2 pureXML, after having obtained the required UUID information from an internal service provider.

A concrete example

The example depicted in Figure 2 builds upon an Industry Formats and Services with pureXML technology demonstration that provides a Web services wrapper for accessing sample HL7 documents stored in DB2 pureXML. We leverage the alphaWorks Web services wrapper and access it from the ESB mediation module. To compose an XQuery on the HL7 documents stored in DB2 pureXML and pass it to the alphaWorks Web services wrapper, you need a drug UUID. Since you cannot assume that the user knows the drug UUID, another internal Web service takes the drug name as input and returns its corresponding UUID. Thus, a single user request with the drug name as input is realized by the mediation module by invoking the two services in sequence. In this way, the service interface exposed by the ESB hides the internal complexities of the solution, including composition of the XQuery before invoking the alphaWorks service. Listing 1 shows the WSDL interface of the service exported to the consumer by the ESB. (The internal service interface is not included here; it is a rather simple Web service returning a string with the drug's UUID.)

Figure 2. Concrete example architecture
Figure 2. Concrete example architecture
Listing 1. WSDL interface for service exported by ESB
<?xml version="1.0" encoding="UTF-8"?>
<wsdl:definitions …>
    <xsd:schema …>
      <xsd:element name="getSPLInfo">
            <xsd:element name="medicationName" nillable="true"
      <xsd:element name="getSPLInfoResponse">
            <xsd:element name="SPLDocument" nillable="true"/>
    <wsdl:message name="getSPLInfoRequestMsg">
    <wsdl:part element="tns:getSPLInfo" name="getSPLInfoParameters"/>
    <wsdl:message name="getSPLInfoResponseMsg">
    <wsdl:part element="tns:getSPLInfoResponse" name="getSPLInfoResult"/>
    <wsdl:portType name="MedicationSPL">
    <wsdl:operation name="getSPLInfo">
      <wsdl:input message="tns:getSPLInfoRequestMsg" name="getSPLInfoRequest"/>
      <wsdl:output message="tns:getSPLInfoResponseMsg" name="getSPLInfoResponse"/>

The alphaWorks demo illustrates how XML -- in particular, industry formats such as HL7 -- can be stored and queried in pureXML databases in a straightforward way without mapping. It also shows how the data can be retrieved through Web services, RESTful services, atom feeds, and XForms. This article uses the Web services option only.

The alphaWorks demo and samples illustrate how a generic service layer can be created to enable access to the stored messages by exposing a simple set of CRUD (Create, Replace, Update, Delete) and Query services. The demo uses DB2 pureXML columns (collections of XML) to store XML documents. If the structure of the XML format evolves, it can continue to be stored in the same table without any significant modifications, such as re-mapping.

The alphaWorks Web services interface provides more than one retrieve method. Two of them are:

  • getXMLDocumentByKey: Returns a specific XML document
  • runxquery: Executes an XQuery on the XML documents in the table

Our example uses the latter method, which uses the DB2 pureXML capability to execute an XQuery on the XML column of the database. In the mediation module, this XQuery is formulated using the UUID of the drug and invokes the Web service method to retrieve the matching drug document.

Listing 2 shows a portion of the alphaWorks HL7 service interface. It shows the definition of the runxquery operation to be invoked on the alphaWorks SPL data service.

Listing 2. WSDL interface of alphaWorks HL7 SPL data service demo
      <element name="runxqueryResponse">
            <element name="queryresult">
                  <element ref="tns:runXQuery"/>
      <element name="runxquery">
            <element name="query" type="string"/>

  <message name="runxquerySoapInput">
    <part element="xsd1:runxquery" name="request"/>
  <message name="runxquerySoapOutput">
    <part element="xsd1:runxqueryResponse" name="response"/>

<portType name="theSoapPortType">
    <operation name="runxquery">
      <input message="tns:runxquerySoapInput"/>
      <output message="tns:runxquerySoapOutput"/>

The mediation module

You can import the complete example as a project interchange file called, available in the download section of this article. Instructions for deploying and running the example are included below.

Our implementation relies on WebSphere ESB mediation capabilities to implement a mediation flow that realizes the described scenario. The mediation flow is responsible for interface translation and service invocation; it invokes the internal service to obtain the drug UUID, which is then used to invoke the alphaWorks service, returning the resulting HL7 document to the caller.

Figure 3. The mediation module
Figure 3. The mediation module

The mediation module contains an export, called MedicationSPLExport, with Web services bindings and a simple interface. The module also has two imports, both of which have Web services bindings:

  • MedicationServiceImport: This import is bound to the internal service to retrieve the unique ID for the required drug that will be used to execute the XQuery.
  • HL7ServiceImport: This import is bound to the alphaWorks SPL data service to retrieve the XML description of the drug, given its unique ID.

As shown in the assembly diagram in Figure 3, the mediation module contains an additional Java Service Component Architecture (SCA) component, called InvokeHL7Service, that represents the code contained in a custom mediation primitive used to invoke the HL7 data service. We will return to this later.

The wiring of the primitives to each other is straightforward. The only wire that requires explanation is the wire between InvokeHL7Service and HL7ServiceImport. This wire enables the custom mediation to use the symbolic name of the service partner in the lookup in order to invoke the corresponding service. In other words, we don't have to hardcode the endpoint address of the invoked service into the custom mediation.

In simple terms, the objective of the mediation flow is to call an internal Web service on the request path and use the information obtained from that service to invoke another Web service as part of the response path. Figure 4 shows the request path of the mediation flow component.

Figure 4. Request path of the mediation flow component
Figure 4. Request path of the mediation flow component

The objective of XSLTranformation1 is to transform the MedicationSPL interface (which is what the mediation module exposes to its clients) into the Medication interface (which is the interface of the internal Web service). A regular XSLT stylesheet is used for the transformation. In the request path, the internal service is called to obtain the UUID, using the name provided in the MedicationSPL. The returned value is the UUID. The UUID (which is hardcoded here for demonstration purposes) is then inserted into the service message object to be used in the response path to invoke the HL7 SPL data service.

Figure 5. Response path of the mediation flow component
Figure 5. Response path of the mediation flow component

The response path of the mediation flow component is shown in Figure 5. The objective of this flow is to use the returned data from the internal service to form an XQuery in order to invoke the HL7 service, using a custom coded mediation primitive to do so. The purpose of this custom mediation primitive is to take the value returned from the internal Web service that is stored in the SMO, use it to build an XQuery, and pass it to the HL7 Web service. Listing 3 depicts the implementation of the custom mediation primitive.

Listing 3. Custom mediation implementation
public DataObject mediate(DataObject input1) {

	System.out.println("In Response Mediation");
	System.out.println("invoke service and extract response payload");
	ServiceManager serviceManager = ServiceManager.INSTANCE;
	BOFactory bofactory = (BOFactory) serviceManager

	System.out.println("Before Locating Service After Getting Object");

	//(1) Building XPath while extracting the payload from the
	// incoming SMO input1.getString("body/GetStringResponse/GetStringReturn")

	String query = "XQUERY declare default element namespace \"urn:hl7-org:v3\";for 
		$doc in db2-fn:xmlcolumn('DB2INST1.HL7.DOCUMENT')/PORP_IN000001
		where $doc/receiver/device/id/@root=\""
			+ input1.getString("body/GetStringResponse/GetStringReturn")
			+ "\" return $doc";
	//(2) Creating Elements for HL7 Service and setting it's payload to the Built Xquery
	DataObject runXquery = bofactory.createByElement(
			"", "runxquery");
	runXquery.setString("query", query);
	System.out.println("After Setting Object");

	System.out.println("invoke HL7 service ");

	//(3) invoking the webservice by locating the service providing the operation name  
	// and passing in the newly created DataObject as the input Parameters 
	Service myService = (Service) ServiceManager.INSTANCE
	DataObject Hl7Response = (DataObject) myService.invoke("runxquery",
	System.out.println("Getting returned from HL7");

	DataObject Hl7ResponseOutPut = Hl7Response.getDataObject("queryresult");

	// (4)Adjusting Return Values for Polocies External
	DataObject message = bofactory.createByMessage(
	DataObject myRes = bofactory.createByElement(

	myRes.setDataObject("SPLDocument", hl7ResponseOutPut);
	message.setDataObject("getSPLInfoResult", myRes);
	input1.setDataObject("body", message);
	System.out.println("leaving Response");

	return input1;

Here is what's happening in Listing 3: The code section labeled (1) extracts the ID from the payload that is returned from the internal Web service. The ID is then used to build the XQuery statement, which will later be used as input to the HL7 service. In section (2), required elements for the HL7 service invocation are created, setting the payload to the built XQuery. Section (3) locates the service using the reference name of the mediation. This is made possible by the wire between InvokeHL7Service and HL7ServiceImport in the mediation module's assembly, described earlier. This wire enables the custom mediation primitive to use a symbolic name for the service that is invoked. The actual invocation returns the HL7 compliant document we want to send back to the client of the entire mediation, which happens in section (4), where we insert the payload from the response of the HL7 Web service into the response message structure of the mediation. The code then replaces the request message in the incoming SMO with the response message, enabling any information in the header context to pass through, and saving the effort of creating all this information again.

Putting it all together

To run and test the application, import the project interchange file from the download section of this article. Be sure to import all projects that are included in the file; it contains an implementation of the internal Web service, as well as the projects related to the mediation module.

This example assumes that your test server is running on port 9081. If that is not the case, you must update the appropriate files to point to the correct address before deploying these projects to the server.

Once you have imported the projects and (if required) updated the endpoint addresses, you can start your test server and add the new MedicationSPLModuleApp and MedicationWSEAR projects, using the Add and remove projects... menu option that appears when right-clicking the server.

To test the mediation module, right-click the MedicationSPLExport_MedicationSPLHttp_Service.wsdl file in the MedicationSPLModule project (you must be in the J2EE perspective for this) and select the Web Services => Test with Web Services Explorer. In the Explorer, enter any string as the drug name to call the getSPLInfo operation (as mentioned earlier, the string you enter is ignored by our test implementation).

Figure 6 shows the Web Services Explorer after running the example. To run the test, your system must be connected to the Internet, since the mediation is calling the HL7 service that is hosted on the alphaWorks site. Be aware that the received response document contains a full HL7-compliant SPL record.

Figure 6. Testing the complete example
Figure 6. Testing the complete example


This article described how to combine WebSphere ESB with DB2 pureXML to simplify access to XML documents stored according to industry specific standards. Although this article contained an example based on the Health Level 7 (HL7) healthcare standard, the approach described is applicable to all the other XML-based industry-specific standards, such as ACORD (insurance), FpML (financial derivatives), FIXML (financial trading), MISMO (mortgages), and others.


Code sampleMedicationProjects.zip47 KB



developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into Business process management on developerWorks

Zone=Business process management, WebSphere, Information Management
ArticleTitle=Using industry standard data formats with WebSphere ESB and DB2 Version 9 pureXML