Transforming JDBC service output using the Query for Multiple Rows as XML operation

Additional Requirements

LiveCycle ES

• Try
• Buy

LiveCycle Workbench ES

• Try
• Learn more

This sample file contains TransformQuery.1.0.xml, which is an exported process and customer.sql, which is a MySQL backup.

Adobe LiveCycle Workbench ES provides a service for accessing relational databases. The JDBC service, found within the Foundation service category, contains multiple query and execution operations for relational data accessible via JDBC.  For orchestrations that require access to multiple rows from a table, the JDBC service delivers the operation called Query for Multiple Rows as XML.

The Query for Multiple Rows as XML operation returns an XML instance where each node has an attribute "type" with a value representing the database schema data type. This article demonstrates how to remove the "type" attribute from the XML instance using the XSLTService Transform operation. 

The use case

Let's assume that an orchestration requires multiple rows of data from a MySQL table called "customer" (see Figure 1).

Based upon the database schema, by default, the "Query for Multiple Rows as XML" service operation returns the following XML instance:

However, the use case may require the XML instance to be formatted differently.  For illustration purposes, the data extracted from the "customer" table will be written to the file system as XML and the XML instance will be formatted as follow:

Draw the orchestration

The process map will contain the following service operations (see Figure 2).

Use the following steps to create the process map by adding and linking instances of the following service operations:

1. Choose Foundation > JDBC > Query for Multiple Rows as XML
2. Choose Foundation > XSLT Transformation > Transform
3. Choose Foundation > File Utilities > Write String

Add process Variables

Now define two process variables (see Figure 3).

Configure the JDBC operation

Follow the steps to configure the JDBC operation:

1. Add a datasource name (see Figure 4).

   The datasource name is a JNDI name defined in a datasource file.  You can add data sources to the datasource file deployed by LiveCycle ES (..\deploy\adobe-ds.xml), or preferably, create "application" datasource files.  Separating datasource files from "adobe-ds.xml" eliminates the introduction of errors into the "system" datasources and optimizes portability, particularly for migration through the application development lifecycle.

2. Add the SQL statement "select * from customer; " to retrieve all columns and rows from the "customer" table.
3. Click the ellipses to the right of the XML information property.
4. Add the root element name "customers" and the repeating element name "customer" (see Figure 5).
5. Click Process Metadata to load the index and column name values.
6. Click Test. The window will load an XML instance based upon a subset of data in the "customer" table.

7. Copy and paste the XML instance from the Test window to a text editor.

   The XML instance will be used in the Transform operation for testing the XSL transformation.  On Windows platforms, avoid using Notepad as a text editor for XML. Notepad adds a Byte-Order-Mark (BOM) which can cause XSL transformations to fail. 

8. Click OK.
9. For the Output property XML document, add the variable customersXml.

Configure the XSLT operation

Follow the steps below to configure the XSLT operation:

1. For the XML Source property Source XML document, add the variable customersXml.
2. From the XSLT Source property XSLT – (literal value), click the ellipses. 
3. Add the following XSL in the XSLT window: 

The template <xsl:template match="/ | node()"> performs matches on two patterns. The pattern / matches the root node and node() matches any node other than an attribute node and the root node.  The copy is then performed on all child nodes excluding attribute nodes. 

4. Click the Test XML tab and copy and paste the XML instance that you saved in Step 7 above.
5. Click the Test Result tab and click Test (see Figure 6).

6. Click OK.
7. For the Transformation Result property Transformed XML, add the variable customersStr.

Configure the file utilities operation

Do the following steps to configure the file utilities operation:

1. Add the fully qualified pathname for the destination XML file (see Figure 7).
2. For the Input property Value, add the variable customersStr.
3. Optionally, select *Over Write to replace the file on each write operation.

Deploy and test the orchestration

Follow the steps below to deploy and test the orchestration:

1. Save and activate the process.
2. Test the orchestration—Open a browser and enter the URL to call the process via the default SOAP endpoint.  For example, if the process name is TransformQuery, the following URL will synchronously invoke a process instance (see Figure 8):

http://localhost:8080/soap/services/TransformQuery?method=invoke

The WS-Security implementation in Axis requires user authentication on the SOAP request. Use administrator/password for user name/password.

The SOAP envelope contains the node invokeResponse indicating the process executed successfully.

The invocation procedure defined above is a simple method to test an orchestration that has no input parameters. More typically, an orchestration deployed in LiveCycle ES will be invoked by a client application using one of the out-of-the-box mechanisms including a Java API, web services, e-mail, and watched folders. Using the LiveCycle Administration Console, a service can be configured to be exposed by one or more of these mechanisms. For more on invoking LiveCycle ES, see the LiveCycle ES SDK Help.

Go to the pathname specified in the Write String service to validate the creation of the file and the XML content.

Where to go from here

Adobe LiveCycle ES can extend your enterprise with new applications that span data capture, process orchestration, and document generation. This article demonstrated the assembly of services into a process without a user interface. Imagine…

• your customers or partners securely accessing a process through an interactive PDF form or Adobe Flex application and the PDF form or Flex application contained pre-populated data
• a process that swaps between a Flex application and a PDF form to optimize the user experience
• a PDF document that could be taken offline, securely shared, and submitted back to the process
• the captured data could then be integrated into a transactional database, a customer relationship management (CRM) system, or an electronic content management (ECM) system

For more on LiveCycle ES, visit the Adobe LiveCycle ES documentation page.

LiveCycle Workbench ES Help provides instructions for importing process versions.

Read MySQL documentation for instructions on how to restore a table and information on the General tab.