The implementation Dataflow looks like below.
We have the Source XML based file. The XML format is imported from the XSD file. Next we have a Query Transform which is used for the purpose to UNNEST the Nested Data Structure, to Denormalize the data set. Next we have two Query Transforms to handle the Employee and Department information separately. Since the data is denormalized we will have to use the DISTINCT clause when loading the employee and department information to target tables respectively.
Select the top-most Schema, Right-Click and select Unnest with sub-schemas
Next to get the Department information select the columns from the Schema In and Map to Output. Next go to SELECT tab and check the selection criteria of Distinct rows.
We follow the same steps to gather the Employee information from the source.
Lastly we have the corresponding Target tables to gather Employee and Department information. Finally we View Data of the Target Tables.