Introduction

The daily operations of today's businesses rely heavily on forms for data collection, including such activities as account opening, claims, requests for service, and even tax filing. Many data collection processes still rely on paper forms to gather information, but both public and private businesses are moving from paper forms to online electronic forms (eForms). Compared to paper-based systems, an eForms solution is faster, more efficient, easier to use, and results in better data. The World Wide Web Consortium (W3C) recommendation for eForms is eXtensible Markup Language (XML) based XForms technology.

XML is considered by many to be the most suitable technology for online data transfer and has been widely used in many industries for online business. The self-describing, flexible, and extensible aspects of XML also make it a perfect choice as the data model for electronic forms data capture and submission. XForms, powered by XML, can present and capture electronic form data in an efficient manner. XForms supports form data validation against an XML schema that can include industry standard vertical vocabularies such as ACORD, HL7, and NIEM. XForms also supports direct XML data submission to a server. Direct XML data submission eliminates the process for data format conversion before transmission. XForms is platform independent, which means one single form development can be deployed to multiple client devices. IBM® Lotus Forms provides an eForms solution based on XForms with additional presentation and security capabilities that can greatly improve the data capture process.

After data is captured by a form, an efficient method to manage the XML form data is needed. Traditionally, in a relational database, XML data has to either be treated as a large object or decomposed into relational columns. When XML data is treated as a large object in a database, any query against the data requires a full XML document retrieval instead of only retrieving the needed data, and the content of the data cannot be used to determine which documents to retrieve. On the other hand, decomposition of XML data into relational columns is a complex and costly process, and it removes the self-descriptive semantic information within the XML. The decomposition process also eliminates the flexibility and extensibility of XML. If the structure of the original XML document is changed, which is likely to happen periodically to form applications, the original mapping to relational structures will no longer be valid and will require updates to incorporate the change.

IBM addressed the need for efficiently managing XML data in relational databases by introducing the pureXML feature in IBM DB2. With DB2 pureXML, you can store XML data into, and retrieve the data from, the database in its original hierarchical structure without further processing or formatting. This enables efficient management for XML form data.

Combining DB2 pureXML and Lotus Forms provides a foundation for a simple yet effective electronic form solution.

Solution overview

The IBM Intelligent eForms solution is built on DB2 pureXML and Lotus Forms. Lotus Forms captures data on the front-end in an XML structure and DB2 pureXML manages the form XML data in the back-end. The front-end and back-end are connected with a Web service created with IBM Data Studio Developer and hosted on an application server such as IBM WebSphere® Application Server. The solution offers end-to-end XML data capture, submission, and management capability with reliable and scalable IBM software products. The solution architecture in Figure 1 shows the tools used at both development time and run time.

The following sections briefly describe the capabilities of the primary products that make up the solution. Refer to the Resources section for links to more detailed information about these products.

DB2 pureXML

The pureXML feature was first introduced in IBM DB2 Version 9. It provides the ability to natively store XML data in its hierarchical format within a relational database system. This means the XML document is stored with full database awareness of the document's internal hierarchical structure and its individual elements and attributes. With pureXML, you can efficiently store, control, query, and update XML data alongside relational data stored in the same table or database.

XML data in DB2 pureXML is presented as a column in a table like other relational data, with a data type of XML. DB2 supports queries written in industry-standard SQL/XML and XQuery languages to access and manipulate the XML data. DB2 services such as optimization, indexing, concurrency control, and utilities are extended to support XML data along with the relational data. Figure 2 depicts how with pureXML, DB2 has become a hybrid database that can access and manage relational data and XML data in the same efficient manner.

With the native XML storage of pureXML, you can take advantage of the flexibility of XML structures and reduce application overhead in your eForms solution. With pureXML, you can insert the complete form XML data into a single DB2 XML column while still maintaining efficient access to individual data elements. You can return whole documents or subsets of documents depending on what is needed. The XML form data stored in DB2 can be directly transmitted over an intranet or the Internet for additional processing, analysis, or information sharing without further manipulation.

Note: pureXML is available starting with Version 9 of DB2 for Linux®, UNIX®, and Windows® and DB2 for z/OS. The solution in this article uses DB2 Express-C edition Version 9.5 for Linux, UNIX, and Windows, which is free to download and use. Refer to the Resources section for a link to the download page.

Data Studio Developer

IBM Data Studio is an Eclipse-based portfolio of tools that includes a comprehensive set of features to design, develop, and manage data, databases, and data-driven applications. It provides a development and testing environment you can use to build database objects and applications. Data Studio Developer allows you to utilize SQL, SQL/XML, XQuery and Java to create and execute database queries, stored procedures, Web services, and Java data access layers. You can generate Data Web Services directly from SQL statements and stored procedures without writing a single line of application code. The Web services support both SOAP and REST bindings and you can immediately deploy them to supported application servers such as WebSphere Application Server.

This article describes how to use Data Studio Developer to create a simple Data Web Service to receive XML data from an eForm and store it directly into DB2.

WebSphere Application Server

IBM WebSphere Application Server is a Java, J2EE, and Web service based application server. It offers a rich application deployment environment with a complete set of application services, including capabilities for transaction management, security, clustering, performance, availability, connectivity, and scalability. The solution in this article uses WebSphere Application Server Community Edition for Web services deployment. WebSphere Application Server Community Edition is free of charge. Refer to the Resources section for a link to the download page.

Lotus Forms

IBM Lotus Forms helps you automate forms-based business processes and facilitates data integration with existing IT systems. The foundation technologies of Lotus Forms are the W3C open standard XForms and Extensible Forms Description Language (XFDL). At a high level, XForms provides an XML data model with separation of form data and appearance, and XFDL controls the form presentation layer and business logic. With Lotus Forms, you can create eForms with a paper form look and feel or with a guided question and answer style. Lotus Forms has capabilities to validate data entry based on defined criteria and to apply business logic based on data that is entered. Lotus Forms also provides digital signature support to ensure security. Lotus Forms can be deployed as client desktop software or as Web server based forms that you can access via a Web browser.

This article describes how to use Lotus Forms Designer and Lotus Forms Viewer. Lotus Forms Designer is the development tool for building XForms based electronic forms. It is built on an Eclipse platform and provides a WYSIWYG environment with advanced features for fast eForms development. Lotus Forms Viewer is the rich desktop runtime client that allows you to view, complete, and submit eForms.

Building the Solution

This section guides you through the steps to create an Electronic Forms solution. These are the three major steps to create the complete electronic forms solution (refer back to Figure 1 for a diagram):

1. Create the database and table to store the form data in DB2 pureXML - use DB2 Command Editor

2. Create and deploy the Data Web Service - use Data Studio Developer

3. Create the electronic form - use Lotus Forms Designer

This whole process can be completed quite quickly once you have the necessary software installed. Then all you need to do is fill in the form and submit the data using Lotus Forms Viewer. The article guides you through this as well.

Finally, the article shows you how to query against the XML form data that gets stored in DB2.

Prerequisites

Before proceeding to building the solution, you need to install and configure the following software on your system:

• DB2 9, or later, including pureXML

• Data Studio Developer V2.1, available as a free trial download

• WebSphere Application Server Community Edition V2.1

• Lotus Forms Designer V3.5

• Lotus Forms Viewer V3.5

Refer to the Resources section for the download links and detailed installation and configuration guidelines of each product.

The article guides you through the steps to build the electronic forms solution. If you plan to create the solution on your system it would be helpful to have a basic knowledge of database systems, DB2, and SQL statements. It would also be beneficial to have a basic understanding of XML technology and Web services concepts.

Create the Database and Table to Store the Form Data

Follow the steps below to create the database and table for the eForms solution in DB2.

1. Open the DB2 Command Editor window and enter the following command to create a database:

   create database eformdb using codeset utf-8 territory us

   
   

2. Enter the following command to connect to the database:

   connect to eformdb

   
   

3. Enter the following command to create a table. For simplicity, the table for this solution has only one column, but notice that the column has a data type of XML.

   create table eformtable (form xml)

   
   

Create and deploy the Data Web Service

Follow the steps below to create a Data Web Service with Data Studio Developer. The Data Web Service receives the XML form data and inserts it into the database. This solution only requires a simple Web service, though more sophisticated services are certainly possible.

1. Launch Data Studio Developer.

2. In the Data Source Explorer window, double click Database Connections > EFORMDB [DB2 Alias] to bring up its properties window (Figure 3). If you do not see the EFORMDB database in the list, right click on the Database Connections item and select New... to create the connection.

3. In the properties window, Select IBM Data Server Driver for JDBC and SQLJ Default from the Drivers drop down menu. Enter your DB2 user name and password and check the Save password box. You can test the connection by clicking the Test Connection button. Click OK to finish.

   
   
   Figure 3: Properties for EFORMDB database connection
   
   
   

4. Right click anywhere in the Data Project Explorer window and select New > Data Development Project to bring up the New Data Development Project window. Enter eFormProject as the project name. Click Next, then select EFORMDB from the Connections list in the Select Connection screen (Figure 4), and click Finish.

   
   
   Figure 4: New Data Development Project window
   
   
   

   Note: Information such as the project name is used in the WSDL created by Data Studio Developer for the Web service, so be aware that it is case sensitive.

5. In the Data Project Explorer window, click the plus sign (+) to expand the eFormProject, right click eFormProject > SQL Scripts, and select New > SQL or XQuery Script to create a new SQL statement. Enter insertForm as the name (Figure 5) and click Finish.

   
   
   Figure 5: New SQL or XQuery Script window
   
   
   

   Note: This solution uses the SQL and XQuery editor (default option) to create the SQL statement. You could also build the statement from the SQL Query Builder using a wizard approach.

6. In the insertForm.sql window, enter the following script:

   insert into eformtable (form) values (:document)

   
   

   Close and save insertForm.sql. You should now be able to see the script listed in the SQL Scripts under eFormProject in the Data Project Explorer.

7. In the Data Project Explorer window, right click eFormProject > Web Services and select New Web Service... to create a Web service. Enter eFormWebService as the name and eForm as the namespace URI (Figure 6). Once again, remember to be careful to use the spelling and capitalization for the values you enter. Click Finish.

   
   
   Figure 6: New Web Service window
   
   
   

8. In the Data Project Explorer window, drag and drop the eFormProject > SQL Scripts > insertForm.sql onto eFormProject > Web Services > eFormWebService*. This creates a new operation in the eFormWebService called insertForm.

   
   
   Figure 7: Drag and drop to create operation for Web service
   
   
   

9. In the Data Project Explorer window, right click eFormProject > Web Services > eFormWebService* and select Build and Deploy... to bring up the Deploy Web Service window (Figure 8). Select the Server radio button and select IBM WASCE v2.1 Server at localhost from the drop down menu. Check the box for Launch Web Services Explorer after deployment and click Finish. (This starts WASCE if it is not already started.)

   
   
   Figure 8: Deploy Web Service window
   
   
   

10. In the Web Services Explorer window (Figure 9), click on the URL under WSDL Main in the Navigator pane and then scroll down in the Actions pane to select Import WSDL To File System. Save the file as eForm.wsdl on your hard drive.

    
    
    Figure 9: Web Service Explorer window
    
    
    

You have now finished creating and deploying the Web service.

Create the electronic form

Follow the steps below to create a simple electronic form.

Note that the Download section of this article contains a link to a sample form. The sample form works with the Web service you created in the previous section as long as you followed the same naming conventions described in the steps. You can use the attached sample form as an alternative to creating your own, or you can create your own and use both to see examples of a simple and complex form. The same Web service and database table work for both.

1. Launch Lotus Forms Designer and select File > New Lotus Form... to bring up the New Lotus Form Wizard. Select A blank form (default option) and click Finish. (If a window pops up and asks if you want to switch perspective, choose No.) Select Window > Open perspective > Other..., choose Advanced Lotus Forms Designer from the list, and then click OK.

2. In the Enclosures window (Figure 10), right click WSDL > WebServices and select Enclose WSDL File. Choose the eForm.wsdl you saved earlier on your hard drive to be attached to the form.

   
   
   Figure 10: Insert WSDL to the form
   
   
   

3. In the Instance window (Figure 11), click on the Creates a new Instance from a WSDL message button.

   Note: The Instance window shows an instance with the name Instance: Generated. However, for this solution you create your own instance from the enclosed WSDL file so that you can submit the form to the Web service.

   
   
   Figure 11: Create instance from enclosed WSDL in the Instance window
   
   
   

4. In the WSDL message window (Figure 12), check the insertFormSoapInput box and click OK. A new instance called INSTANCE is created.

   
   
   Figure 12: Select the needed operation in the WSDL Message window
   
   
   

5. Switch to Source view by clicking the Source tab at the bottom of the canvas (Figure 13). Find the xforms:instance id="INSTANCE" tag that was created from the WSDL message. As shown in Figure 13, change the child elements of the <document> element to include the following:

   <document> <data> <name> <firstname></firstname> <lastname></lastname> </name> </data> </document>

   
   
   
   Figure 13: Change the INSTANCE in Source view
   
   
   

6. Switch back to Design view by clicking the Design tab at the bottom of the canvas. In the Instance window (Figure 14), expand the Instance: INSTANCE item and then drag and drop the data element into the canvas. This automatically creates the corresponding label and entry fields for the data element and its descendants. The highlighted Refs column next to each element indicates the element is bound to an entry field on the form and the number shows how many times the element is referred to.

   
   
   Figure 14: Drag and drop element to create labels, entry fields and binding
   
   
   

7. In the XForms window (Figure 15), right click XForms > Model: Default > instance: INSTANCE and select Create Submission for Instance. This creates a new submission called SUBMISSION, which is the procedure to submit the form data to the Web service.

   
   
   Figure 15: Create submission functionality for the form
   
   
   

8. Switch to Source view. Find and change the xforms:submission tag so that it looks like the following:

   <xforms:submission id="SUBMISSION" replace="none" method="post" action="http://localhost:8080/eFormProjecteFormWebService/Services/eFormWebService" mediatype="application/soap+xml action=eForm/insertForm"> </xforms:submission>

   
   

   The value of the action attribute above is the URL of the SOAP binding for the Web service. The URL can be found in the soap:address tag within the enclosed WSDL message as shown in Figure 16.

   
   
   Figure 16: Find the soap:address in the enclosed WSDL
   
   
   

   The action within the mediatype attribute is the name of the SOAP operation. This can be found in the soap:operation tag within the enclosed WSDL message as shown in Figure 17. Make sure that the value of action in the mediatype attribute is not surrounded by quotation marks.

   
   
   Figure 17: Find the soap:operation in the enclosed WSDL
   
   
   

   Note: For a complex Web service, there may be multiple soap:operation items. Find the soap:operation that you used to create the instance. In this case, since you picked insertFormSoapInput in Figure 12 when creating the instance, you choose the eForm/insertForm operation in Figure 17.

9. Switch back to the Design view. In the Palette window under Advanced Library (Figure 18), select Button (Submit) and click in the canvas to create a submit button.

   
   
   Figure 18: Use Palette to create a submit button
   
   
   

   You may encounter a pop up Properties dialog (Figure 19) that allows you to set the properties of the object you just created. Change the properties as you wish and click OK.

   
   
   Figure 19: Properties dialog may pop up when creating new objects
   
   
   

10. With the submit button selected on the canvas, expand the XForms (Submit) item in the Properties window (Figure 20). Use the drop down menu to point the submission option to the submission you created in the step above, which is SUBMISSION.

    
    
    Figure 20: Point the submit button to the submission we created
    
    
    

11. In the Enclosures window (Figure 21), right click WSDL > WebServices > eForm.wsdl then select Delete to delete the WSDL message from the form.

    
    
    Figure 21: Enclosed WSDL need to be deleted
    
    
    

12. Save the form as eForm.xfdl and close Lotus Forms Designer.

Submit eForm Data

You are now ready to use the eForm to capture information and submit data to the database via the Web service. Follow the steps below:

1. Open the eForm.xfdl with Lotus Forms Viewer. Input some sample data and click the Submit button to send the form to the database via the Web service.

2. Fill out and submit a few more sample forms. The query examples in the next section are based off of the sample data shown in Table 1.

3. When you are done submitting samples, simply close Lotus Forms Viewer.

Query eForm XML Data from the Database

After form data has been submitted, you can query the XML form data that was inserted into DB2. DB2 Version 9 supports both SQL/XML and XQuery for querying XML. This example uses a SQL/XML query. For a detailed explanation of SQL/XML and XQuery statements, see the Resources section.

Open the DB2 Command Editor window or Command Line Processor (or use Data Studio, creating a new SQL Script for this query). Input and execute the SQL/XML statements below.

1. The following query finds the userid from all submitted forms and returns them as varchar:

   select xmlcast(xmlquery('$f/data/userid/text()'passing form as "f") as varchar(15)) as ID from eformtable

   
   

   Given the sample data from Table 1, the query result from the above statement executed in the DB2 Command Editor window is shown in Figure 22.

   
   
   Figure 22: Query result for sample query 1
   
   
   

2. The following query finds the information of all the users with a last name of Smith, and returns the data in relational format:

   select t.fname as "First Name", t.lname as "Last Name", t.id as "User ID", t.phone as "Phone" from eformtable, xmltable('$d/data' passing form as "d" columns fname varchar(20) path 'name/firstname', lname varchar(20) path 'name/lastname', id varchar(20) path 'userid', phone varchar(20) path 'phone') as t where xmlexists('$d/data/name[lastname="Smith"]' passing form as "d")

   
   

   Given the sample data from Table 1, the query result from the above statements executed in the DB2 Command Editor window is shown in Figure 23. If you did not use this same sample data, remove or change the xmlexists where predicate.

   
   
   Figure 23: Query result for sample query 2
   
   
   

Note: IBM also provides advanced business intelligence tools such as IBM DataQuant and Cognos for generating comprehensive reports with XML data in DB2. Refer to the DB2 pureXML wiki link in the Resources section for more information.

Summary

DB2 pureXML and Lotus Forms, connected by Web services, can be combined to address the need for efficient electronic forms based data collection and management. This article illustrates how an electronic forms solution can be created with minimal effort by using the three industry leading development tools from IBM: DB2 pureXML, Data Studio Developer, and Lotus Forms. The solution provides state-of-the-art technologies for eForms design and submission (Lotus Forms), Web services development and deployment (Data Studio Developer), and form XML data storage, control, and access (DB2 pureXML). The simplicity of this eForms solution is due to the fact that is based on end-to-end XML data flow: data is captured in Lotus Forms as XML, submitted to a Web service in that same XML format, and stored directly into DB2 pureXML without any intermediate conversions or manipulation. The end-to-end XML flow simplifies not only the solution development process but also the execution flow. This solution can be implemented quite simply but can greatly improve the effectiveness of information capture, management, and analysis for public and private enterprises.

Acknowledgements

The authors would like to thank those who provided input to or reviewed this article, in particular Eric Dunn who provided valuable guidance on Lotus Forms.

Back to top

Download

Information about download methods

Resources

Learn

• "Demo: Build an intelligent eForms solution based on DB2 pureXML, Lotus Forms, and Web services": Watch a demo that summarizes the steps discussed in this article.
  
  
• "DB2 9 pureXML: Overview and Fast Start": A free IBM Redbook that introduces DB2 pureXML and its query language support.
  
  
• "Best Practices - Managing XML Data": Learn DB2 pureXML best practices.
  
  
• DB2 pureXML wiki: Get more articles, success stories, news items, and demos for DB2 pureXML.
  
  
• Data Studio: Find out more about IBM integrated data management software.
  
  
• WebSphere Application Server: Learn how IBM WebSphere Application Server can drive your business agility.
  
  
• Lotus Forms: Discover how IBM Lotus Forms speed process automation.
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  

Get products and technologies

• Get started with free DB2 V9.5 Express-C today, which includes pureXML.
  
  
• Try out Data Studio Developer for your database development with a free trial download.
  
  
• Download WebSphere Application Server Community Edition at no charge.
  
  
• Get Lotus Forms to develop your own eForms, with a free trial download.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Get involved in the DB2 pureXML Forum.
  
  

About the authors

Bryan Patterson is a senior solutions architect at IBM's Silicon Valley Laboratory who specializes in database management. He has more than 20 years of software industry experience including management positions in development, quality assurance, and product planning. His email address is bryanp@us.ibm.com.

Dexiong Terry Zhang is a software engineer Co-op at IBM's Silicon Valley Lab. He graduated from San Jose State University with a degree in Computer Science. He joined the DB2 pureXML Enablement team in 2008 and is working on solution integration of pureXML in various projects.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Solution overview
• Building the Solution
• Summary
• Acknowledgements
• Download
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: Data Studio
• Demo: Use Ajax with DB2
• Briefing: Virtual tech briefings on integrated data management
• Community: DB2
• Buy: Data Studio

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers