Search code examples
oracle-databasetemplatesrtfbi-publisheroracle-xml-publisher

RTF Template to get rows to columns

<EMPLOYEE_INFORMATION_REC>
<PERSON_NUMBER>31</PERSON_NUMBER>
<Payroll_Relationship_Number>531</Payroll_Relationship_Number>
<Payroll_ID>322623279</Payroll_ID>
<PersonID>305634180</PersonID>
<Payroll>
<PERSONAL_PAYMENTS>
    <Payroll_Relationship_Rec>
    <Payroll_Relationship_Action_ID>326491</Payroll_Relationship_Action_ID>
    <Payroll_Interface_Effective_Date>2023-05-25T00:00:00.000Z</Payroll_Interface_Effective_Date>
    <Payroll_Interface_Base_Run_Type_Name>Regular Normal</Payroll_Interface_Base_Run_Type_Name>
    <Tax_Reporting_Unit>Saskatchewan Workers' Compensation Board</Tax_Reporting_Unit>
    <Action_Type>Q</Action_Type>
<Run_Results>
<Run_Results_Rec>
    <Input_Values_Base_Name>Hours</Input_Values_Base_Name>
    <Run_Result_Value>2.45</Run_Result_Value>
    <Base_Element_Name>Overtime 2x Result</Base_Element_Name>
    <Start_date>2023-05-15</Start_date>
    <End_date>2023-05-15</End_date>
    <Run_Result_ID>1530959</Run_Result_ID>
    <Primary_Classification_Name>Standard Earnings</Primary_Classification_Name>
    <PERSONID>300000015630330</PERSONID>
</Run_Results_Rec>
</Run_Results>
<Run_Results>
<Run_Results_Rec>
    <Input_Values_Base_Name>Hours</Input_Values_Base_Name>
    <Run_Result_Value>4.45</Run_Result_Value>
    <Base_Element_Name>Overtime 2x Result</Base_Element_Name>
    <Start_date>2023-05-17</Start_date>
    <End_date>2023-05-17</End_date>
    <Run_Result_ID>1530949</Run_Result_ID>
    <Primary_Classification_Name>Standard Earnings</Primary_Classification_Name>
    <PERSONID>300000015630330</PERSONID>
</Run_Results_Rec>
</Run_Results>
</Payroll_Relationship_Rec>
</PERSONAL_PAYMENTS>
</Payroll>
</EMPLOYEE_INFORMATION_REC>

I want to display the above XML in such a way that these Input_Values_Base_Name ,Run_Result_Value,Base_Element_Name etc come in column instead of rows in the RTF template.

How to tweak the below template for the same ?

Person_number                                                   Input_Values_Base_Name          Base_Element_Name                                                           
<?for-each:EMPLOYEE_INFORMATION_REC?> <PERSON_NUMBER>           <Input_Values_Base_Name>         <Base_Element_Name> <?end for-each?>

expectation -

PERSON_NUMBER Input_values_base_name Base_Element_Name Start_date
31 Hours Overtime Result 2023-05-17
31 Hours Value Result 2023-05-15
Solution

  • If you use Word to create rtf template then create table, create repeating group and add columns. BI Publisher Properties are in red : enter image description here

    And if you need xsl code to be applied to style your xml then:

    <?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
  <html>
  <body>
    <xsl:for-each select="//EMPLOYEE_INFORMATION_REC">
        <br/>
        <table>
          <tr>
            <th align="right" style="border: solid black 1px;">PERSON_NUMBER</th>
            <th align="left" style="border: solid black 1px;">Input_Values_Base_Name</th>
            <th align="left" style="border: solid black 1px;">Base_Element_Name</th>
            <th align="left" style="border: solid black 1px;">Start_date</th>
          </tr>
          <xsl:for-each select="//Run_Results_Rec">
              <tr>
                <td style="border: solid black 1px;"><xsl:value-of select="//PERSON_NUMBER" /></td>
                <td style="border: solid black 1px;"><xsl:value-of select="Input_Values_Base_Name" /></td>
                <td style="border: solid black 1px;"><xsl:value-of select="Base_Element_Name" /></td>
                <td style="border: solid black 1px;"><xsl:value-of select="Start_date" /></td>
              </tr>
          </xsl:for-each>
        </table>
    </xsl:for-each>
  </body>
  </html>
</xsl:template>
</xsl:stylesheet>