XML file generation using SAP Data Services

The target XML file will look something like below-

<XML_EMP_DEPT></XML_EMP_DEPT>
	<DEPARTMENT>
		<DEPTNO>99<DEPTNO>
		<DNAME>XXXX<DNAME>
		<EMPLOYEE>
			 <EMPNO>9999<EMPNO>
			 <ENAME>XXXX<ENAME>
			 <SAL>9999.99<SAL>
			 <COMM>9999.99<COMM>
		<EMPLOYEE>
		<LOC> XXXX <LOC>
	<DEPARTMENT>
<XML_EMP_DEPT>

Now in this particular scenario suppose we want to display only those departments which have employees. Which means there can be department information but they may not have any employees.So Lets get started.

Below is the implementation Dataflow for target XML file generation from Source tables EMP and DEPT. It consists of two source tables, a Row Generation and a Query transform followed by target XML file.

Join the two source tables and the Row Generation to the Query transform. For the Row Generation transform the Row number starts at 0 and Row count is set to 1. This will be used to generate the header xml tag. Next open the Query Editor.

Select the Root Schema of the Schema Out. Go to the FROM tab. Select only the Row Generation as the Input Schema. Next right-click the root schema of Schema Out and select New Output Schema.

Let's name the New Output Schema as DEPARTMENT. Right-click the New Output Schema and select Make Current. This schema will hold the department information. Go to the From tab and select only the DEPT table as the Input Schema.

Next in this example we want to generate output file having employee & department information for only those departments where employee exists. Hence we go to the WHERE tab and apply the filter criteria as Where DEPT.DEPTNO IN ( SELECT DISTINCT DEPTNO FROM EMP ). Next we select the DEPTNO and DNAME from the DEPT Input Schema and Map to Output. Next right-click and select New Output Schema.

Let's name this Schema as EMPLOYEE. Select this New Output Schema as right-click select Make Current. Go to the FROM tab and select only the EMP from the Input Schemas.

Next go to the WHERE tab. We want to display the Employee information with along with its parent Department. So for each department we want to view the corresponding employee information for that department. So we use the filter condition as EMP.DEPTNO = DEPT.DEPTNO. Next select the columns from EMP Input Schema and map to Output. Finally we again make the DEPARTMENT schema as CURRENT and then select the LOC column from DEPT Input Schema and Map to Output, to generate our desired XML Schema.

We are done with the Dataflow design. This is how the Source Tables Data looks like:

After successful execution of this Dataflow within a Job we get the desired XML file as Output. Below is the sample Hierarchical data set generated through the dataflow we designed.

Sample Output Employee Data within the Parent Department Hierarchical Data.