How do I get related data from another XML row?

Question

I am trying to create an Oracle BI Publisher report (using the Microsoft Word plugin). The report is being created from an XML file which is created from PeopleSoft. The XML files contains many rows (I've included some sample data). One row contains employee data and the next row contains beneficiary data. In the XML file it appears that every odd numbered row is employee data and every even number row is beneficiary data. In building the report, what I need to do is display all necessary data from the Beneficiary row and only the Amount (A. CALCULATED_BASE) from the employee row. This should appear as one block of output, not two.

I can get my report to show data from either the employee's row or the beneficiary's row, or both rows, but I cannot figure out how to show all necessary data from the beneficiary's data and only the Amount from the employee's data.

I'm not sure if this is the best way to solve this, but I've being trying to figure out how to use "preceding-sibling" to get A.CALCULATED_BASE from the employee row. This is what I am thinking: I'll read in beneficiary data and then look at the other rows and compate A.PLAN_TYPE. If A.PLAN_TYPE for the beneficiary row is equal to A.PLAN_TYPE in the employee row then get A.CALCULATED_BASE. The way to determine if a row is for the employee or the beneficiary is to look at A.RELATIONSHIP. A.RELATIONSHIP is always empty for the employee but contains data for the beneficiary.

Sample XML Data:

<?xml version='1.0'?>
<query numrows="10" queryname="query_name" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="">
<row rownumber="1"> <!-- Employee -->
    <A.PLAN_TYPE><![CDATA[10]]></A.PLAN_TYPE>
    <A.NAME><![CDATA[Firstname Lastname]]></A.NAME>
    <A.RELATIONSHIP><![CDATA[]]></A.RELATIONSHIP>
    <A.CALCULATED_BASE>10000</A.CALCULATED_BASE>
    <A.CONTINGENT><![CDATA[N]]></A.CONTINGENT>
</row>
<row rownumber="2"> <!-- Beneficiary -->
    <A.PLAN_TYPE><![CDATA[10]]></A.PLAN_TYPE>
    <A.NAME><![CDATA[Firstname Lastname]]></A.NAME>
    <A.RELATIONSHIP><![CDATA[Spouse]]></A.RELATIONSHIP>
    <A.CALCULATED_BASE>0</A.CALCULATED_BASE>
    <A.CONTINGENT><![CDATA[N]]></A.CONTINGENT>
</row>
</query>

In the example below:

The amount comes from A.CALCULATED_BASE from the employee row, and the rest of the data comes from the Beneficiary row.
PLAN_TYPE 10 is shown as "Life Insurance".
The beneficiary status Primary comes from A.CONTINGENT.
A.RELATIONSHIP is used to determine if the data is for an employee or beneficiary.

Output Example:

Employee's Name and other information goes here.

Life Insurance                                  Amount: $10000.00
Beneficiary             Beneficiary Status          
Beneficiary Name        Primary

Thanks for any help.

michael.hor257k's answer might work in this case, but do we have a guarantee that there will always be only one beneficiary ? There could be multiple beneficiaries to a single employee ? With different relationships ? In which case the best logic might be to find the last employee record. — Ranjith R, Mar 02 '17 at 05:07

score 0 · Answer 1 · answered Mar 01 '17 at 19:02

every odd numbered row is employee data and every even number row is beneficiary data. In building the report, what I need to do is display all necessary data from the Beneficiary row and only the Amount (A. CALCULATED_BASE) from the employee row.

Here's a simplified example showing the basic approach you need to take:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:template match="/query">
    <root>
        <!-- for every even row -->
        <xsl:for-each select="row[position() mod 2 = 0]">
            <record>
                <!-- get beneficiary data -->
                <name>
                    <xsl:value-of select="A.NAME" />
                </name>
                <relationship>
                    <xsl:value-of select="A.RELATIONSHIP" />
                </relationship>
                <!-- get employee data -->
                <base>
                    <xsl:value-of select="preceding-sibling::row/A.CALCULATED_BASE" />
                </base>
            </record>
        </xsl:for-each>
    </root>
</xsl:template>

</xsl:stylesheet>

Applied to your input example, the result will be:

<?xml version="1.0" encoding="UTF-8"?>
<root>
   <record>
      <name>Firstname Lastname</name>
      <relationship>Spouse</relationship>
      <base>10000</base>
   </record>
</root>

Hi, thanks for this feed back. This is similar to what I was thinking but we ended up using the table wizard and condition fields to get this working. — , Mar 08 '17 at 18:01

score 0 · Accepted Answer · answered Mar 08 '17 at 17:59

sorry for taking so long to get back to you. I've been away for a bit. Anyway, we got this report working using the table wizard and a bunch of conditional regions. I'm not sure if this is the best method (I'm really new to this) but it does appear to be properly working. We'll do more testing to verify. Using this method also took into account multiple beneficiaries.

Thanks, again for your feedback.

How do I get related data from another XML row?

2 Answers2