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

See Also

In most cases the client XML does not have the same format as the XML required by xml.import to import data into multiple tables. In this case, however, the client XML must be transformed (using the USXSL component). The following XSL example will show you how to transform XML from a parent-child-grandchild XML structure to the structure required by USoft.

Assuming that the client XML has the following format:

<Companies>

   <Company NAME="CA">

       <Department NAME="Sales">

               <Employee NAME="Person1"/>

               <Employee NAME="Person2"/>

       </Department>

       <Department NAME="Engineering">

               <Employee NAME="Person3"/>

               <Employee NAME="Person4"/>

       </Department>

   </Company>  

   <Company NAME="CB">

       <Department NAME="Support">

               <Employee NAME="Person5"/>

               <Employee NAME="Person6"/>

       </Department>

       <Department NAME="Education">

               <Employee NAME="Person7"/>

               <Employee NAME="Person8"/>

       </Department>

   </Company>  

</Companies>

This must be transformed into an XML document that looks like the following:

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

<MultiImport>

   <Companies>

       <COMPANY NAME="CA" Company.id="1"/>

       <COMPANY NAME="CB" Company.id="2"/>

   </Companies>

   <Departments>

       <DEPARTMENT NAME="Sales" Department.id="3">

           <RELATE ROLE="HAS" href="#1"/>

       </DEPARTMENT>

       <DEPARTMENT NAME="Engineering" Department.id="4">

           <RELATE ROLE="HAS" href="#1"/>

       </DEPARTMENT>

       <DEPARTMENT NAME="Support" Department.id="5">

           <RELATE ROLE="HAS" href="#2"/>

       </DEPARTMENT>

       <DEPARTMENT NAME="Education" Department.id="6">

           <RELATE ROLE="HAS" href="#2"/>

       </DEPARTMENT>

   </Departments>

   <Employees>

       <EMPLOYEE NAME="Person1">

           <RELATE ROLE="HAS" href="#3"/>

       </EMPLOYEE>

       <EMPLOYEE NAME="Person2">

           <RELATE ROLE="HAS" href="#3"/>

       </EMPLOYEE>

       <EMPLOYEE NAME="Person3">

           <RELATE ROLE="HAS" href="#4"/>

       </EMPLOYEE>

       <EMPLOYEE NAME="Person4">

           <RELATE ROLE="HAS" href="#4"/>

       </EMPLOYEE>

       <EMPLOYEE NAME="Person5">

           <RELATE ROLE="HAS" href="#5"/>

       </EMPLOYEE>

       <EMPLOYEE NAME="Person6">

           <RELATE ROLE="HAS" href="#5"/>

       </EMPLOYEE>

       <EMPLOYEE NAME="Person7">

           <RELATE ROLE="HAS" href="#6"/>

       </EMPLOYEE>

       <EMPLOYEE NAME="Person8">

           <RELATE ROLE="HAS" href="#6"/>

       </EMPLOYEE>

   </Employees>

</MultiImport>

id-href values do not have to be numbers, they only have to be unique in the document. The output of the following transformation will generate some unique values using the generate-id xslt function for the id-href values.

<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="/">

<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="Companies">

               <xsl:apply-templates select="Companies/Company" 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">COMPANY</xsl:with-param>                  

               </xsl:apply-templates>

           </xsl:element>

           <xsl:element name="Departments">

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

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

                   <xsl:with-param name="is_child">true</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="Companies/Company/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: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(name(), '.'), '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>