Writing data to an XDM File

Overview

This guide explains how to use a hook to retrieve data from a database and write it to an XDM file.

It covers both character-based data types, such as NVARCHAR and CLOB, and BLOB data. Since the implementation differs only in a few processing steps, both variants are described together.

The guide first explains the common workflow using NVARCHAR as the reference implementation. The BLOB-specific handling is described in the corresponding code section, where the retrieved binary data is Base64-encoded before being written to the output file.

The guide provides the required code examples, parameters, and configuration details for both variants.

Prerequisites

Before proceeding, ensure that the following objects exist:

A valid database connection in XDM as source.
An XDM file object of type Simple text as target.
Read access to the source database table.

Steps

Step 1: Define the Hook

Create a Task Stage Hook.

The hook contains the following parameters:

Display Name Data Type Description

Display Name	Data Type	Description
`databaseConnection`	`CONNECTION`	The database connection in XDM.
`tableSchema`	`STRING`	The schema where the table is located.
`tableName`	`STRING`	The name of the table containing the data column.
`dataColumn`	`STRING`	The column storing the required data.
`condition`	`STRING`	The SQL condition used to select the correct row.
`outputFile`	`FILE`	The XDM file where the data will be stored.
`isBlob`	`BOOLEAN`	Specifies whether the selected column contains BLOB data (`true`) or character-based data (`false`).

databaseConnection

CONNECTION

The database connection in XDM.

tableSchema

STRING

The schema where the table is located.

tableName

STRING

The name of the table containing the data column.

dataColumn

STRING

The column storing the required data.

condition

STRING

The SQL condition used to select the correct row.

outputFile

FILE

The XDM file where the data will be stored.

isBlob

BOOLEAN

Specifies whether the selected column contains BLOB data (true) or character-based data (false).

The connection and the file of type Simple text must already exist in XDM. The remaining parameters are used to build the SQL query.

The hook is designed so that the query returns exactly one row.

The code of the hook is as follows:

import de.ubs.xdm3.script.evaluator.ScriptValidationException
import org.apache.commons.codec.binary.Base64

def query = "SELECT " + taskStageHook.dataColumn +
            " AS DATAVALUE FROM " + taskStageHook.tableSchema +
            "." + taskStageHook.tableName +
            " WHERE " + taskStageHook.condition

println("Query is: " + query)

def preparedStatement = taskStageHook.databaseConnection.jdbcConnection.prepareStatement(query)
def resultSet = preparedStatement.executeQuery()

if (resultSet.next()) {
    if (taskStageHook.isBlob) {
        blob = resultSet.getBlob("DATAVALUE")
        blobBytes = blob.getBinaryStream().readAllBytes()
        base64String = Base64.encodeBase64(blobBytes)

        taskStageHook.outputFile.content = base64String
    } else {
        text = resultSet.getString("DATAVALUE")

        taskStageHook.outputFile.content = text
    }
} else {
    throw new ScriptValidationException("No data found in query")
}

resultSet.close()
preparedStatement.close()

The data is selected from the database and written into the XDM file depending on the selected processing mode.

For character-based data, the database value is read as a string and directly transferred to the output file. The resulting file contains the original text content from the database.

For BLOB data, the value is read as binary data, Base64-encoded, and then written to the XDM file.

The isBlob parameter determines how the retrieved value is processed:

false: The value is treated as character-based data.
true: The value is treated as BLOB data and encoded as Base64.

The SQL query is built dynamically from parameters. Only use trusted parameter values or adapt the hook to validate schema, table, column, and condition values before execution.

The complete hook can be found here:

Writing database data to an XDM File

Step 2: Execute the Hook in a Workflow

Call the hook using HookRunner() in a workflow.

HookRunner()
    .hook('Writing Database Data to an XDM File')
    .parameter('databaseConnection', '<Connection in XDM>')
    .parameter('tableSchema', '<Table schema>')
    .parameter('tableName', '<Table name>')
    .parameter('dataColumn', '<Data column>')
    .parameter('condition', '<Condition>')
    .parameter('outputFile', '<XDM file object>')
    .parameter('isBlob', '<true/false>')
    .run()

Replace the placeholders with the actual values from your database or with the display names of the XDM objects.

The database connection and output file must already exist in XDM before executing the workflow.

Set isBlob according to the data type:

false for character-based columns, such as NVARCHAR or CLOB.
true for BLOB columns.

Step 3: Validate the Output

Check the XDM file after workflow execution.

For character-based data, verify that the expected string content has been written.

For BLOB data, verify that the file contains the Base64 representation of the selected binary value.

Benefits

This approach provides the following benefits:

Automates the retrieval of character-based or BLOB data.
Avoids manual local file handling.
Can be reused for different tables, columns, and output files.

Conclusion

This guide shows how to retrieve database content with a hook and write it to an XDM file.

Character-based data can be written directly, while BLOB data is Base64-encoded before being stored.

By using this approach, database-to-file extraction can be automated and integrated into XDM workflows.