Database Mapping

Map and Convert Data between Databases or Other Data Formats

  • Execute database queries on all major databases
  • Support for relational & NoSQL databases
  • Conversion from one database type to another
  • Map database data to or from other data formats
  • Specify database key settings
  • Output logging & error handling
  • Define database table actions
  • Query databases directly
  • Connect to and map database XML in tables
  • Sort database input components

Database Mapping Tools

MapForce is the data mapping and integration tool with powerful support for database conversion. You can convert between any combination of database data and XML, JSON, PDF, CSV and other flat files, EDI, Excel (OOXML), protobuf, XBRL, Web services, and even other database formats.

When you load a database structure in the design window, MapForce automatically interprets the database schema, lets you pick available database tables and views, and recognizes table relationships.

Graphical database mapping in MapForce to easily integrate database data

Once you have loaded all of the content models required for your database mapping, complete the mapping by simply dragging connecting lines between the source and target structures.

Supported relational databases:

  • Firebird 2.5, 3, 4
  • IBM DB2 for iSeries® v6.1, 7.1, 7.2, 7.3, 7.4
  • IBM DB2® 8, 9.1, 9.5, 9.7, 10.1, 10.5, 11.5, 11.5.7
  • Informix® 11.70, 12.10, 14.10
  • MariaDB 10, 10.3, 10.4, 10.5, 10.6.5, 10.9.2, 11.2
  • Microsoft Access™ 2003, 2007, 2010, 2013, 2019
  • Microsoft® Azure SQL
  • Microsoft® SQL Server® 2005-2022
  • MySQL® 5, 5.1, 5.5, 5.6, 5.7, 8, 8.0.25, 8.0.28, 8.2, 8.3
  • Oracle® 9i, 10g, 11g, 12c, 18, 19
  • PostgreSQL 8, 9, 10, 11, 12, 13, 14, 15, 16
  • Progress OpenEdge 11.6
  • SQLite 3.x
  • Sybase® ASE 15, 16
  • Teradata 16

Supported NoSQL Databases:

  • MongoDB
  • CouchDB

MapForce supports all major relational databases, as well as popular NoSQL databases, empowering you to create graphical database mapping designs between database source data, data processing functions and filters, and other data structures of various types. This allows support for common database migration scenarios, such as MySQL to PostgreSQL, as well as countless other possibilities including one-to-many and chained data transformations.

Filtering and Processing Database Data

Many database mappings require manipulation of data between the source and target based on Boolean conditions or SQL and SQL/XML statements. You may need to perform logical comparisons, mathematical computations, or string operations, check for database data of a particular value, and make other modifications to the data. In screenshot above, data processing functions appear as the boxes between the lines joining the source and the target data model.

Data processing functions enable you perform advanced database mappings on-the-fly for a multitude of real-world transformation requirements. You can, for example, construct database mappings that use XML or EDI messages to extract database rows based on filter criteria from the XML or EDI elements.

Instant Database Conversion

Once your mapping is defined, the built-in MapForce Engine allows you to view and save the results with one click.

Database-to-XML mappings produce an XML output document, database mappings to flat files have output in CSV or fixed-length text files, mappings of databases to EDI can produce EDIFACT, X12, or HL7 messages, database mappings to Excel produce Office Open XML (OOXML) markup, and mappings to XBRL produce XBRL financial reports.

Mappings to a database produce output in the form of SQL scripts (e.g., SELECT, INSERT, UPDATE, and DELETE statements) or NoSQL scripts that are run against your target database directly from within MapForce.

Database output logging (tracing)

Database output tracing makes it easy to log all the changes made by a mapping project to the database when the mapping runs.

When tracing is on, events such as database insert or update actions, or errors, are logged in an XML file that you can later analyze or process further in an automated way.

Database tracing can be enabled at the database component, table, stored procedure, or database field level. You can choose to trace all messages or only errors, or you can disable tracing completely.

Once you have selected the desired tracing level, the affected database component in the mapping project is extended with an XML tree showing the log structure. If you prefer the log file to be in a format other than XML, you can map data from it to some other data format supported by MapForce (for example, a text file or even another database).

When clicking the Output button to preview the result of the mapping, the developer can also see an example of what the trace log will look like when the mapping is executed.

Database tracing to log output for database conversion

Database transaction error handling

MapForce also offers database transaction handling to roll back the affected part of the database conversion when an error is thrown and optionally proceed with the rest of the mapping.

Sometimes an error occurs that doesn’t prevent the rest of a database mapping from continuing, such as when certain database constraints prevent the mapping from inserting or updating invalid data.

Enabling database transaction handling will enclose all changes done by the database component inside a transaction that will be rolled back in case of error, and you can opt to either proceed with rest of the mapping or stop further execution of the mapping. You can enable transactions at database component level and at table action level.

Enabling database transactions for error handling

This is very useful when the mapping designer wants to capture valid incoming data and simply ignore invalid data.

Advanced Database Mapping

To complement its database mapping and integration capabilities, MapForce includes a Database Query tab for performing direct database queries. When you connect to a database using the Database Query tab, MapForce displays its tables in a hierarchical tree in the browser pane, with specialized support for the following databases:

  • Firebird
  • IBM DB2 for iSeries
  • IBM DB2®
  • Informix®
  • MariaDB
  • Microsoft Access™
  • Microsoft® Azure SQL
  • Microsoft® SQL Server®
  • MySQL®
  • Oracle®
  • PostgreSQL
  • Progress OpenEdge
  • SQLite
  • Sybase®
  • Teradata
  • MongoDB
  • CouchDB
Query the database while defining a database conversion mapping

You can then use the SQL editor tab to display, edit, and execute SQL or SQL/XML statements, either by opening existing SQL files or creating SQL statements from scratch using drag and drop and auto-complete functionality.

You can execute your SQL script and view the results in tabular form and save both the retrieved data and the SQL script individually to separate files.

Database Key Settings

The MapForce database key settings allow you to customize how primary and foreign key values will be added to a database that is a data mapping target. You can either provide values for keys from within MapForce, or you can let the database system handle the generation of auto-values.

In situations where primary and/or foreign key relationships are not explicitly defined in your database tables, MapForce allows you to define these relationships inline, without any effect on the source data.

Define Database Table Actions

When you are mapping to a database, MapForce allows you to select database table actions to control how data is written to the database. This gives you full flexibility to automate the most advanced data management tasks.

The easy-to-use Database Table Actions dialog box allows you to define the columns within the selected table to be used to determine what action (INSERT, UPDATE, DELETE) should be executed in the database.

This provides unprecedented flexibility in manipulating database rows in response to XML, database, EDI, XBRL, flat file, Excel, JSON, JSON5, Web services, or other database data through MapForce.

Support for SQL Stored Procedures

MapForce includes robust support for stored procedures as input components (procedures that provide results) or as output components (procedures that insert or update data). Or, stored procedures may be inserted as a function-like call allowing users to provide input data, execute the stored procedure, and read/map the output data to other components.

This screenshot shows the mapping of a stored procedure in SQL Server to create an XML file. The procedure returns a table of data showing all the managers in the chain of command above the specified employee ID, supplied as an input parameter – in this example the constant 67.

The parameter could also be supplied as a calculated value or a data element retrieved from elsewhere in the database.

MapForce provides a context menu that lets users run the stored procedure to reveal the data structure for mapping. Executing the illustrated mapping generates the XML output.

Advanced data mapping tools include stored procedure support

Map Database XML

MapForce also allows you to connect to and map database XML stored in relational database fields (currently supported for SQL Server & IBM DB2). You simply assign an XML Schema – either one registered in the database or one from your local file system – to the field, and MapForce renders the schema as a sub-tree of the database field for mapping purposes.

Sort Database Input Components

SQL queries that operate inside the database are not always sufficient for complex data mapping tasks. MapForce provides additional database sorting functionality through the SQL-WHERE/ORDER component for database input that requires additional processing, or when other data or conditions in the mapping impact the sorted order of data rows.

Support for NULL Values in Database Table Actions

The Database Table Actions dialog supports NULL value comparison. NULL-aware comparisons provide an improved way to handle databases that contains null values. MapForce users can configure a database mapping so that data comparison is done in a NULL-aware manner, according to rules applicable to the database kind involved in the mapping.

The data mapping shown at right is intended to update the target table without inserting duplicate entries. Both tables are defined allowing the email field to be NULL, so matching name entries with NULL email fields could exist in each table.

Clicking the Actions icon next to the TARGET database table opens the Database Actions Dialog. The NULL equal checkbox next to the email field allows MapForce to treat NULL values in the source and target as equal for data mapping purposes, even though they are not considered equal by database rules.

Null-aware database mapping in MapForce

Database to XML

MapForce supports database mappings to or from XML based on XML Schema or DTD content models. To develop a database mapping to or from XML, simply an XML Schema and database into MapForce and drag connecting lines between XML nodes and database objects.

If you do not have an associated schema for an XML instance document, MapForce generates an XML Schema from an XML instance document.

MapForce XML Database Mapping

Support for XML Wildcards

The <xs:any> element and <xs:anyAttribute> in an XML Schema design allow any new element or attribute to be placed at the corresponding location in an XML instance document, even though the new element or attribute is not defined in the XML Schema. This is known as an XML wildcard and it is a popular mechanism used to allow a degree of customization in many XML Schemas that support industry standards across a wide variety of businesses.

MapForce supports <xs:any> and <xs:anyAttribute> for mapping to output in XML or any other output format. A new selection button next to <xs:any> or <xs:anyAttribute> in an XML mapping input component opens a wildcard selection dialog.

The <xs:any> element, as well as <xs:anyAttribute>, are commonly used in XML Schema design and support in MapForce has been a frequent user request.

Learn how to convert database data to XML and other formats in MapForce

JSON Database Mapping

MapForce includes support for defining and executing database mappings based on JSON (JavaScript Object Notation) models. You can add JSON instance or JSON schema files as source or target components of a database mapping. MapForce reads and writes JSON files based on the JSON Draft 04 Schema.

As shown below, JSON components are displayed with appropriate element syntax and their data types are clearly indicated.

Convert database data to JSON in MapForce

Data processing functions from the MapForce Function Library can be applied to transform JSON data, exactly as they are used with other components.

Autogenerate JSON Schemas

When you add a JSON or JSON5 file to a database mapping, MapForce detects automatically whether it is a schema or instance file. For JSON or JSON5 instance files, MapForce prompts you to browse for a schema or generate one automatically. MapForce uses the JSON or JSON5 schema to build the structure of the component.

Automate Database Mapping Execution with MapForce Server

Altova MapForce Server includes the built-in data transformation engine developed for MapForce and is greatly enhanced to operate in server environments. MapForce Server performs data transformations for any combination of XML, database, EDI, XBRL, flat file, Excel, JSON, and/or Web service using preprocessed and optimized data mappings stored in execution files based on data mappings defined in MapForce. MapForce Server takes data transformation to the next level with server capabilities including parallel processing and multi-threading, bulk SQL merge, cross-platform support, and more.

Preprocessing enables faster performance and reduced memory footprint for most data mappings. MapForce Server operates under the management of FlowForce Server, in a standalone configuration executed from a command line, or programmatically via an API.

After a MapForce mapping is designed and tested, it can be executed by MapForce Server to automate business processes that require repetitive data transformations.

Automate database mapping tasks via MapForce

MapForce pre-processes and optimizes data mappings, stores them in MapForce Server Execution files for command-line execution by MapForce Server, and uploads them for use in FlowForce Server jobs.

When MapForce Server operates under the management of FlowForce Server, data mappings are executed as FlowForce Server job steps, based on triggers defined as part of the FlowForce Server job. For example, a new XBRL instance document lands in a directory, which triggers a multi-step FlowForce Server job to first validate the file using RaptorXML+XBRL Server, then execute MapForce Server to extract certain data from the XBRL and insert it into a database.

MapForce Server Supports Bulk Insert for Databases

Bulk Insert is an operation available for certain databases that allows a large volume of data to be inserted into a database table in a single SQL statement, as opposed to the typical method of using individual Insert statements for each row. Since processing overhead by the database engine is greatly reduced, performance is much faster. Testing MapForce Server with some examples has shown Bulk Insert can be more than 10 times faster than individual Insert statements.

Bulk Insert is also advantageous in multi-user environments. A long series of Insert commands transmitted to the database by one user could potentially be interrupted by another user sending a Select request to the same table, then the Insert sequence would continue. In a situation like this the Select operation would result in incomplete or invalid data.