Using MapForce to insert hierarchical XML to relational tables

Mapforce 2014

In an earlier blog post, I demonstrated a way to insert a hierarchical XML document into a relational table structure by using XQuery. That solution works well in smaller solutions, but it’s a “code only” solution that can become complex and hard to maintain in larger projects.

An alternative solution would be to use the graphical mapping tool Mapforce from Altova.

I’m going to use the same source XML and target SQL as in the previous blog post.

The source is the following XML document containing invoices and invoice rows (download the document here but change the file type to .xml):

<Invoices>
	<Invoice>
		<InvoiceNumber>991001</InvoiceNumber>
		<InvoiceDate>2013-12-19</InvoiceDate>
			<InvoiceRows>
				<InvoiceRow>
					<Product>PROD1234</Product>
					<Amount>1000</Amount>
				</InvoiceRow>
				<InvoiceRow>
					<Product>PROD5678</Product>
					<Amount>100</Amount>
				</InvoiceRow>
			</InvoiceRows>
	</Invoice>
	<Invoice>
		<InvoiceNumber>991002</InvoiceNumber>
		<InvoiceDate>2013-12-20</InvoiceDate>
			<InvoiceRows>
				<InvoiceRow>
					<Product>PROD1234</Product>
					<Amount>2000</Amount>
				</InvoiceRow>
			</InvoiceRows>
	</Invoice>
</Invoices>

The target is the following SQL Server tables:

XML Invoices Table Design

XML Invoices Table Design

The sql script that creates the database can be found here.

An evaluation version of MapForce can be downloaded here. The download is the so called MissionKit package, which is a bundle of more software than just MapForce. Among those XMLSpy, a XML editor that is a favorite of mine.

Once downloaded and installed, create a new Mapping. File -> New:

Mapforce New Mapping

New Mapping

Add the XML document to the solution by selecting Insert -> XML Schema/File from the menu. Since we selected a XML file and not a schema, MapForce will ask if you want it to generate the schema for you:

Mapforce Create Schema

Create Schema

If you already have a schema file, select the XSD instead. Once loaded, the XML source schema can be seen in the workspace:

Mapforce XML Source

XML Source

The next step is to load the SQL Server target tables. Select Insert -> Database and select Microsoft SQL Server in the wizard that opens and press Next:

Mapforce Insert Database Connection Wizard

Insert Database Connection Wizard

Select Microsoft OLE DB Provider for SQL Server on the next screen:

Mapforce Insert Database Connecting To MS SQL Server

Insert Database Connecting To MS SQL Server

On the next screen, create a connection to the XMLInvoices database and press OK:

Mapforce Insert Database Data Link Properties

Insert Database Data Link Properties

This will open the Insert Database Objects window. Check the Invoices and InvoiceRows tables to import them to MapForce, and press OK:

Mapforce Insert Database Objects

Insert Database Objects

The tables are displayed in the workspace:

Mapforce Relational Tables

Relational Tables

The first thing we need to do is to map the Invoice and InvoiceRow XML nodes to their respective tables. Drag a connection between the nodes and tables as shown below:

Mapforce Mapping XML Nodes To Tables

Mapping XML Nodes To Tables

Since the element names match the column names in the database, those mappings will be done for you automatically. Just drag connections between elements and columns as needed if the names don’t match.

Also make sure you use the tables that has the parent child representation of Invoices and InvoiceRows:

Mapforce Table Hierarchies

Table Hierarchies

To see the results of the mapping, select the Output tab:

Mapforce Mapping Output

Mapping Output

Mapforce SQL Output

SQL Output

As you can see, the hierarchy is maintained in the resulting SQL script. To run the script, select Output -> Run SQL-Script. However, MapForce also has a powerful code generator. So if you want the code for reuse or scheduling, select File -> Generate Code in…

To see how the hierarchy is controlled, select the little button next to the table name InvoiceRows:

Mapforce Mapping Actions

Mapping Actions

This opens the Database Table Actions window. As you can see, Mapforce has recognized that there is a relation between the tables to maintain, and has automatically selected the correct action for new rows:

Mapforce Database Table Actions

Database Table Actions

The default behavior is to always insert new rows. But if your requirement is that already existing rows are ignored or updated, this is where those actions can be defined.

Leave a Reply

Required fields are marked *.