Introduction

If you are using  Excel input to generate XOG files, this may be interesting for you.

The Excel file is often created from a SQL query.

I developed some tooling that converts Excel input to a simple XML format, that is processed with XSLT to output XOG files.

The idea is simple:

Every row in the Excel file will be converted to an intermediate XML file.

This xml instance will look like:

<table>
     <row>
         <col1>value</col1>
         <col2>value</col2>
     </row>

</table>

In this example <col1> and <col2> are the column names that are found in the Excel sheet.

Your stylesheet will be processed against each of this intermediate XML files to generate to desired output XML.

Repeating elements

In  some XOG file formats are repeating elements. Think of a project XOG which can have multiple resources.

If you have Excel input you can choose to have multiple rows for the same project for each resource.

You can then use the group function in the tooling to get an intermediate XML which contains all the rows for the project.

But this may be getting very complex when you have more than one group of repeating elements.

Using the LISTAGG function of Oracle

The idea of this approach is that there is one line per project in the resulting Excel file by aggregating the repeating elements (persons allocated to the project) into one column.

Look at the following SQL query:

select proj.id         as project_id,

       proj.name       as project_name,

       (select LISTAGG(res.unique_name,',') WITHIN GROUP (ORDER BY res.unique_name)

                from  NIKU_MGR.SRM_RESOURCES res

          inner join  NIKU_MGR.PRTEAM team on team.prResourceId = res.id

          where team.prProjectId = proj.id 

            and res.resource_type = 0

            and res.first_name  is not null)  as team

 from inv_investments proj                  

  where proj.ODF_OBJECT_CODE = 'project'

       and proj.name like 'Clarity Release%'

  order by proj.id;

The LISTAGG  function takes a separator and will concatenate all output into one result column.

In the xslt we must split the team column into the separate persons id values.

We can do that using the tokenize function.

<xsl:for-each select="tokenize($team,',')">
     <person><xsl:value-of select="normalize-space(.)"/></person>
  </xsl:for-each>

Complete documentation