Skip to Content
Author's profile photo Titto Antony

An approach to build Target file in Excel format in SAP BODS using XSL

In this blog i would like to explain an approach to build the target file in the desired Excel format using the xsl style sheet.  As we are aware SAP BusinessObjects Data Services accesses Excel workbooks as sources only (not as targets). So to overcome this limitation we can adopt this approach to display our output in the desired excel format with the help of XSL.

Details on the approach

In this approach we will be building a xml file using the BODS and will be displaying the xml content in the desired tabular format with the help to XSL.

So first we have to create a batch job that creates a xml which contain the required data. Special care must be taken while designing the Xml structure that holds the data need to be displayed in tabular structure. Consider this excel structure in the below example.


In this we have two tabular structure one to hold the header part and second to hold the category part. So when we define the xml structure in the BODS we need to create two schema to hold the Header tabular information and Category tabular information.And these schema will hold the records that need to be populated in the target.So for our sample scenario the xml structure will be as follows


Next we have to build the xsl to describes how to display an XML document. An XSL style sheet is, like with CSS, a file that describes how to display an XML document of a given type.XML does not use predefined tags (we can use any tag-names we like), and therefore the meaning of each tag is not well understood. So a without an XSL sheet browser does not know how to display xml document.

XSL consists of three parts:

XSLT – a language for transforming XML documents

XPath – a language for navigating in XML documents

XSL-FO – a language for formatting XML documents

The root element that declares the document to be an XSL style sheet is <xsl:stylesheet>


An XSL style sheet consists of one or more set of rules that are called templates.A template contains rules to apply when a specified node is matched.

The <xsl:template> element is used to build templates.The match attribute is used to associate a template with an XML element.(match=”/” defines the whole document. i.e. The match=”/” attribute associates the template with the root of the XML source document.)


The XSL <xsl:for-each> element can be used to select every XML element of a specified node-set. So we cab specify how to display values coming in that specified note-set. Considering our sample scenario we can select every element in the Header & Category schema to mention how to display values coming inside that node set.The <xsl:value-of> element can be used to extract the value of an XML element and add it to the output stream of the transformation.

xsl_for each.JPG

After building the xsl file we need to place that file in the target folder where BODS will be building the target file. And we also need to alter the XML header in the target XML structure inside the job. Default Header defined in the XML header will be <?xml version=”1.0″ encoding = “UTF-8″ ?> we need to change that to<?xml version=”1.0” encoding = “UTF-8″ ?><?xml-stylesheet type=”text/xsl” href=”<xsl_fileName>”?>


And  In our target XML, hearder will be like this


Target xml generated after the execution of the job can be opened with Excel. where you will promted with option to open the xml after applying the stylesheet. And  in that we need select our stylesheet to get the output in the desired Excel format.


And our output in Excel will be displayed as given below


Note: Both the XSL file and the xml target file should be available in the same folder for getting the desired output.

Attaching the sample xsl and xml file for reference.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Sandeep Chandran
      Sandeep Chandran

      Inventive Titto.. Great solution.. !


      Sandeep Chandran

      Author's profile photo Former Member
      Former Member

      this is really healpful....

      way to go titto... !!



      Author's profile photo Santhosh Thoutam
      Santhosh Thoutam

      This is a real help. Thanks Titto!



      Author's profile photo Former Member
      Former Member

      Great article. Very helpful



      Author's profile photo Sumesh Sivanandan Pai
      Sumesh Sivanandan Pai

      Very Helpful

      Author's profile photo Ted Kwon
      Ted Kwon

      So good sample of data transformation in XML

      Author's profile photo Former Member
      Former Member

      This is great and very informational. Can you also send me a sample .xsd file you used in your DS job to create the Xml file ?

      Author's profile photo Titto Antony
      Titto Antony
      Blog Post Author

      Sorry neena i didnt keep the back up of xsd. You can generate that xsd from BODS itself using the Generate XML Schema option in the Query transform. First generate the target structure and in the Query transform and generate teh XMl Schema. PFA for referenceTst.jpg

      Author's profile photo Former Member
      Former Member

      Thanks a bunch

      Author's profile photo Sylendra Prasad
      Sylendra Prasad

      Hi Titto,

      Can this approach be applied for creating multiple worksheets in an Excelsheet ?

      How do you create an XML which can open up an Excelsheet with multiple worksheets ? Have you tried it out before ?