This article will demonstrate how to generate XML target files using SAP Data Services. Here our objective is to generate XML file with employee and department information.

The target XML file will look something like below-

<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.

Sample Dataflow to generate XML target 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.

Query Transform- Root Schema

Lets 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.

Query Transform- DEPARTMENT 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.

Query Transform- DEPARTMENT Schema WHERE clause

Lets 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.

Query Transform- EMPLOYEE Schema

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.

Query Transform- EMPLOYEE Schema WHERE clause

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

Sample Source Tables Data

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.

Target XML file- DEPARTMENT level

Sample Output Employee Data within the Parent Department Hierarchical Data.

Target XML file- EMPLOYEE level


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.