Example - Relational to Hierarchical Transformation
The Finance department of the Electronics Superstore company must process paychecks for company employees. They need to transform employee data stored in a relational database into a hierarchical format that their payment system can process.
The mapping needs to use a Relational to Hierarchical transformation that inputs employee details such as employee name, employee ID, employee address, and employee bank account data, and outputs the details in a usable hierarchical format.
In the relational input, the Bank_ID element is a primary key in the Employee table, and a Foreign key in the Bank table:
Employee_ID | Last_Name | First_Name | Address | Bank_ID | Bank_Account |
|---|
9173327437 | Sandrine | Jacques | 74 Mobile Avenue | 74845 | 8723487234 |
9174562342 | Race | Tom | 266 Crouse St. | 9234734 | 45324734 |
8484526471 | Jones | Charles | 3815 LaValle Boulevard | 389236 | 234638437 |
7023847265 | Smith | Delilah | 193 Short Drive | 74845 | 8723463432 |
9174596725 | Frederick | George | 17 Serenity Road | 9234734 | 6342636699 |
Bank_ID | Bank_Name | SWIFT_Code |
|---|
74845 | National Bank | 9173327 |
9234734 | International Bank | 9174562 |
389236 | Star National Bank | 8484526 |
In the Payment output in hierarchical format, the elements are combined from the tables:
<banks>
<bank name="National Bank" SWIFT="9173327">
<account id="8723487234">
<employee_id>9173327437</employee_id>
<fname>Sandrine</fname>
<lname>Jacques</lname>
<address>74 Mobile Avenue</address>
</account>
<account id="8723463432">
<employee_id>9082745558</employee_id>
<fname>Delilah</fname>
<lname>Smith</lname>
<address>193 Short Drive</address>
</account>
</bank>
<bank name="International Bank" SWIFT="9174562">
<accounts>
<account id="45324734">
<employee_id>5534398889</employee_id>
<fname>Race</fname>
<lname>Tom</lname>
<address>266 Crouse St.</address>
</account>
<account id="6342636699">
<employee_id>9174596725</employee_id>
<fname>Frederick</fname>
<lname>George</lname>
<address>17 Serenity Road</address>
</account>
</accounts>
</bank>
<bank name="Star National Bank" SWIFT="8484526">
<accounts>
<account id="234638437">
<employee_id>8484526471</employee_id>
<fname>Jones</fname>
<lname>Charles</lname>
<address>3815 LaValle Boulevard</address>
</account>
</accounts>
</bank>
</banks>
The following image shows the mapping in this example:
The mapping contains the following objects:
- Read_BankAccounts
- The source that contains the bank data.
- Read_Employees
- The source that contains the employee data.
- BankAccountsAndEmployees_To_PaymentsSystemXML
- A Relational to Hierarchical transformation that transforms relational input that contains employee and bank account information into an XML format that the payment system consumes.
- Write_BanksXML
- A target path to the file that stores the transformed data every time you run the mapping.
The mapping uses the Read_BankAccount and Read_Employees files to provide relational input. The mapping processes and transforms the data with the BankAccountsAndEmployees_To_PaymentsSystemXML transformation. Then the mapping stores the output in the target path listed in the Write_BanksXML flat file.
