Example: How to Transform a Parent-Child XML Document to the Required Import Structure

Importing external data into a table for which USoft generates new and unique primary key values can be a problem if that external data also contains child records for a related child table. In that case, you need to be able to tell USoft to generate the correct foreign keys in child records during import, so that the parent-child relations are preserved.

This is achieved by a protocol that involves a special RELATE keyword in the import XML.

This strategy is best explained by an example. The example below simply shows a parent-child structure, but along the same lines, you can also successfully import more complicated structures, such as parent-child-grandchild, or parent-with-2-types-of-children.

One popular format for expressing parent-child relations in XML is to present the child data in child XML nodes that appear below the parent data. This is the format used in the following external XML document. The first two Employees belong to the SALES department. The next two Employees belong to the MARKETING department:

<New_Employees>

<Employee FIRST_NAME="Andy" FAMILY_NAME="CHINGHAM" ADDRESS="244 ROSE AVENUE" CITY="TORONTO" COUNTRY="CA"

AREA_CODE="T4F 2DR" GUIDE="N" HIRE_DATE="2001-03-01T00:00:00" BANK_ACCOUNT="123456789"/>

<Employee FIRST_NAME="Merelyn" FAMILY_NAME="MADESON" ADDRESS="14 BUSH GROVE" CITY="TORONTO" COUNTRY="UK"

AREA_CODE="R4M 5DR" GUIDE="N" HIRE_DATE="2009-06-01T00:00:00" BANK_ACCOUNT="483766455"/>

</Department>

<Employee FIRST_NAME="Denis" FAMILY_NAME="LINDON" ADDRESS="14E AVE DES CHANTEURS" CITY="PARIS" COUNTRY="FR"

AREA_CODE="75005" GUIDE="N" HIRE_DATE="2010-03-01T00:00:00" BANK_ACCOUNT="998763401"/>

<Employee FIRST_NAME="Peter" FAMILY_NAME="DE VRIES" ADDRESS="PRINSENGRACHT 551" CITY="AMSTERDAM" COUNTRY="NL"

AREA_CODE="1018AB" GUIDE="N" HIRE_DATE="2015-06-01T00:00:00" BANK_ACCOUNT="887666321"/>

</Department>

</New_Employees>

To process this input successfully in USoft, it must be presented like the XML document below, which meets the following requirements:

•The header area of the XML must have a "usoft-xml" processing instruction as shown.

•The root node must be <MultiImport>.

•Each parent of a parent-child relationship must be given a dummy number or string, presented as the value of an XML attribute named "<TABLE>.id", where both the table name and the letters "id" are case-sensitive, for example, "DEPARTMENT.id", but not "Department.ID".

•The child of a parent-child relationship must have a <RELATE> child element that names the ROLE of the Relationship with the parent as known by USoft, as well as an href attribute that serves to identify the dummy number of the associated parent.

For example:

<?xml version="1.0" encoding="UTF-8"?>

<?usoft-xml version="1.0" action="multi-tables-import" use-io-formats="no" verify-original-values="no" return-corrected-records ="yes"?>

</Departments>

</EMPLOYEE>

</EMPLOYEE>

</EMPLOYEE>

</EMPLOYEE>

</Employees>

</MultiImport>

The following example XSLT will generate the required XML when applied to the example input shown at the top of this help topic:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/New_Employees">

<xsl:processing-instruction name="usoft-xml">version="1.0" action="multi-tables-import" use-io-formats="no" verify-original-values="no" return-corrected-records ="yes"</xsl:processing-instruction>

<xsl:element name="MultiImport">

<xsl:element name="Departments">

<xsl:apply-templates select="Department" mode="copy">

<xsl:with-param name="is_parent">true</xsl:with-param>

<xsl:with-param name="is_child">false</xsl:with-param>

<xsl:with-param name="table_name">DEPARTMENT</xsl:with-param>

<xsl:with-param name="role">HAS</xsl:with-param>

</xsl:apply-templates>

</xsl:element>

<xsl:element name="Employees">

<xsl:apply-templates select="Department/Employee" mode="copy">

<xsl:with-param name="is_parent">false</xsl:with-param>

<xsl:with-param name="is_child">true</xsl:with-param>

<xsl:with-param name="table_name">EMPLOYEE</xsl:with-param>

<xsl:with-param name="role">HAS</xsl:with-param>

</xsl:apply-templates>

</xsl:element>

</xsl:template>

<xsl:template match="*" mode="copy">

<xsl:param name="is_parent"/>

<xsl:param name="is_child"/>

<xsl:param name="table_name"/>

<xsl:param name="role"/>

<xsl:element name="{$table_name}">

<xsl:for-each select="@*">

<xsl:copy/>

</xsl:for-each>

<xsl:if test="$is_parent='true'">

<xsl:variable name="id" select="concat(concat(translate(name(),'abcdefghijklmnopqrstuvwxyz','ABCDEFGHIJKLMNOPQRSTUVWXYZ'), '.'), 'id')"/>

<xsl:attribute name="{$id}">

<xsl:value-of select="generate-id()"/>

</xsl:attribute>

</xsl:if>

<xsl:if test="$is_child='true'">

<xsl:element name="RELATE">

<xsl:attribute name="ROLE">

<xsl:value-of select="$role"/>

</xsl:attribute>

<xsl:attribute name="href">

<xsl:text>#</xsl:text>

<xsl:value-of select="generate-id(parent::*)"/>

</xsl:attribute>

</xsl:element>

</xsl:if>

</xsl:element>

</xsl:template>

</xsl:stylesheet>