Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
By continuing you indicate that you agree to Terms of Service and Privacy Policy of the site.
SAP Data Services

XML file generation using SAP Data Services

Updated on Oct 01, 2020

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></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
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- Row_Generation Input Schema
Query Transform- Row_Generation Input 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.

Query Transform- DEPARTMENT Schema FROM
Query Transform- DEPARTMENT Schema FROM

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
Query Transform- DEPARTMENT Schema WHERE clause

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.

Query Transform- EMPLOYEE Schema
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
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
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
Target XML file- DEPARTMENT level

Sample Output Employee Data within the Parent Department Hierarchical Data.

Target XML file- EMPLOYEE level
Target XML file- EMPLOYEE level