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