Database Mapping

Map and Transform Data between Databases or Other Data Formats

  • Execute database queries on all major databases
  • Map data from one database type to another
  • Map database data to or from other data formats
  • Specify database key settings
  • Define database table actions
  • Query databases directly
  • Connect to and map database XML in tables
  • Sort database input components

Database Mapping Features

MapForce is the data mapping and integration tool with powerful support for database mapping, including mapping between any of database data and XML, JSON, flat files, EDI, Excel (OOXML), Protocol Buffers, 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.

MapForce Database Mapping

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 Databases:

  • Firebird 2.5, 3
  • IBM DB2 for iSeries® v6.1, 7.1
  • IBM DB2® 8, 9.1, 9.5, 9.7, 10.1, 10.5
  • Informix® 11.70, 12.10
  • MariaDB 10, 10.3
  • Microsoft Access™ 2003, 2007, 2010, 2013
  • Microsoft® Azure SQL
  • Microsoft® SQL Server® 2005, 2008, 2012, 2014, 2016, 2017
  • MySQL® 5, 5.1, 5.5, 5.6, 5.7, 8
  • Oracle® 9i, 10g, 11g, 12c
  • PostgreSQL 8, 9.0.10, 9.1.6, 9.2.1, 9.4, 9.6, 10
  • Progress OpenEdge 11.6
  • SQLite 3.x
  • Sybase® ASE 15, 16
  • Teradata 16

MapForce supports all major relational 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.

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) that are run against your target database directly from within MapForce.

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.

MapForce Database Query

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.

MapForce Stored Procedure

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 data mapping in MapForce

Database Mapping to or from 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.

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.

MapForce JSON Database Mapping

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.

Creating a MapForce Server Execution File

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.