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.

  • Using MERGE Transform in SAP Data Services

    This tutorial describes how to use MERGE transform in SAP Data Services (formerly known as BusinessObjects Data Services). A short video is also provided to provide hands-on.

  • Text Data Processing using SAP Data Services

    This article deals with Text Data Processing using SAP Business Objects Data Services with the intension of Text Analytics. SAP BODS provides a single ETL platform for both Structured and Unstructured data as well as Data Quality, Data Profiling...

  • Data Services Metadata Query Part 3

    This article is a continuation of the previous topic related to Data Services Metadata Query. Let us explore more into the Data Services Repository Metadata.

  • GROUP RANK in Data Services

    In this article, we will learn how to implement RANK and DENSE RANK operations on GROUP in SAP Data Services.

  • Working with Data Services Flatfiles

    In this article we will try to cover all the possible scenarios related to flatfiles in SAP Data Services.

  • Learning SAP Data Services - Online Tutorial

    We are pleased to start this new tutorial page for SAP BusinessObjects Data Services (BODS). If you do not know SAP Data Services (BODS) yet but wish to master this ETL tool, you have come to the right place. Even if you are already familiar with...

  • Why SAP Data Services

    SAP BusinessObjects Data Services delivers a single enterprise-class solution for Data Integration, Data Quality, Data Profiling, and Text Data Processing that allows us to integrate, transform, improve, and deliver trusted data to critical...

  • Data Services Scenario Questions Part 6

    In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.

  • Table Comparison Transform to Implement Slowly Change Dimension (SCD) in Data Service

    In this tutorial we will learn a new SAP Data services transform, known as Table Comparison Transform and we will see how we may use this transform to implement "slowly changing dimension" (SCD) Type - I. Like before, we have added a video tutorial at the...

  • Map_CDC_Operation in Data Services

    Map CDC Operation is used to support relational or mainframe changed-data capture (CDC). This transform reads the source DML changes i.e. INSERT/UPDATE/DELETE and reflects the same in the target system. So the transform maps all the source rows...