Search code examples
xmloracle-databasexsltbi-publisher

How can I regroup xml master and detail format


Requirement

How can i fix following xml report using xsl, it outputs this

current report output

where desire out should be as following image

desire report output

Description:

I'm using xsl transform to format xml containing vendor, voucher and Invoice Information, however each has Master/detail relation. Please have a look and suggest how to get desire output.

Thanks in Advance.

XSLT

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="html" />
    <xsl:template match="LIST_G_VENDOR">
        <body>
            <h2>Vendor Details</h2>
            <table border="1">
                <tr bgcolor="#9acd32">
                    <th>Vendor Name</th>
                    <th>Vendor Number</th>
                    <th>Voucher Number</th>
                    <th>Inv Type Number</th>
                    <th>Inv Number</th>
                </tr>
                <xsl:for-each select="G_VENDOR">
                    <tr>
                        <td>
                            <xsl:value-of select="C_VENDOR_NAME" />
                        </td>
                        <td>
                            <xsl:value-of select="C_VENDOR_NUMBER" />
                        </td>
                        <xsl:for-each select="LIST_G_DETAIL/G_DETAIL">
                            <td>
                                <xsl:value-of select="C_VOUCHER_NUMBER" />
                            </td>
                            <td>
                                <xsl:value-of select="C_INVOICE_TYPE" />
                            </td>
                            <td>
                                <xsl:value-of select="C_INVOICE_NUMBER" />
                            </td>
                        </xsl:for-each>
                    </tr>
                </xsl:for-each>
            </table>
        </body>
    </xsl:template>
</xsl:stylesheet>

Input XML

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="iar.xsl" ?>
<APXINAGE>
    <LIST_G_PRMBRK>
        <G_PRMBRK>
            <C_PRIMARY_BRK_REAL>3 PL SOL</C_PRIMARY_BRK_REAL>
            <C_PRIMARY_BRK>3 PL SOL</C_PRIMARY_BRK>
            <LIST_G_VENDOR>
                <G_VENDOR>
                    <C_VENDOR_NUMBER>10000012</C_VENDOR_NUMBER>
                    <C_VENDOR_NAME>3 PL SOL</C_VENDOR_NAME>
                    <LIST_G_DETAIL>
                        <G_DETAIL>
                            <C_VOUCHER_NUMBER>23103</C_VOUCHER_NUMBER>
                            <C_INVOICE_TYPE>STANDARD</C_INVOICE_TYPE>
                            <C_DUE_DATE>09-AUG-17</C_DUE_DATE>
                            <C_INVOICE_NUMBER>test2</C_INVOICE_NUMBER>
                            <C_REFERENCE_NUMBER>1</C_REFERENCE_NUMBER>
                            <C_PAYMENT_SCHED_ID>113677</C_PAYMENT_SCHED_ID>
                            <C_AMT_DUE_REMAINING>20000</C_AMT_DUE_REMAINING>
                        </G_DETAIL>
                    </LIST_G_DETAIL>
                    <C_SUM_V_DUE_REMAINING>20000</C_SUM_V_DUE_REMAINING>
                    <C_PER_V_INV_AMT_4>100</C_PER_V_INV_AMT_4>
                </G_VENDOR>
            </LIST_G_VENDOR>
            <C_PER_INV_DUE_AMT_4>100</C_PER_INV_DUE_AMT_4>
            <C_SUM_INV_DUE_AMT_4_F> 20,000.00 </C_SUM_INV_DUE_AMT_4_F>
        </G_PRMBRK>
        <G_PRMBRK>
            <C_PRIMARY_BRK_REAL>7 STAR</C_PRIMARY_BRK_REAL>
            <C_PRIMARY_BRK>7 STAR</C_PRIMARY_BRK>
            <LIST_G_VENDOR>
                <G_VENDOR>
                    <C_VENDOR_NAME_BRK>7 STAR</C_VENDOR_NAME_BRK>
                    <C_VENDOR_ID>1008</C_VENDOR_ID>
                    <C_VENDOR_SITE_CODE_BRK>Faisalabad</C_VENDOR_SITE_CODE_BRK>
                    <C_VENDOR_SITE_CODE>Faisalabad</C_VENDOR_SITE_CODE>
                    <C_VENDOR_NUMBER>10000009</C_VENDOR_NUMBER>
                    <C_CONTACT_SITE_ID>10</C_CONTACT_SITE_ID>
                    <C_VENDOR_NAME>7 STAR</C_VENDOR_NAME>
                    <C_VENDOR_CITY>Faisalabad</C_VENDOR_CITY>
                    <C_VENDOR_STATE>
                    </C_VENDOR_STATE>
                    <C_SHORT_VENDOR_NAME>7 STAR</C_SHORT_VENDOR_NAME>
                    <C_ADDRESS_ID>10</C_ADDRESS_ID>
                    <C_CONTACT_LINE></C_CONTACT_LINE>
                    <LIST_G_DETAIL>
                        <G_DETAIL>
                            <C_VOUCHER_NUMBER>1177</C_VOUCHER_NUMBER>
                            <C_INVOICE_TYPE>STANDARD</C_INVOICE_TYPE>
                            <C_DUE_DATE>15-NOV-16</C_DUE_DATE>
                            <C_INVOICE_NUMBER>test</C_INVOICE_NUMBER>
                            <C_REFERENCE_NUMBER>1</C_REFERENCE_NUMBER>
                            <C_PAYMENT_SCHED_ID>33170</C_PAYMENT_SCHED_ID>
                            <C_AMT_DUE_REMAINING>20000</C_AMT_DUE_REMAINING>
                        </G_DETAIL>
                        <G_DETAIL>
                            <C_VOUCHER_NUMBER>23079</C_VOUCHER_NUMBER>
                            <C_INVOICE_TYPE>STANDARD</C_INVOICE_TYPE>
                            <C_DUE_DATE>18-MAY-17</C_DUE_DATE>
                            <C_INVOICE_NUMBER>Test3</C_INVOICE_NUMBER>
                            <C_REFERENCE_NUMBER>1</C_REFERENCE_NUMBER>
                            <C_PAYMENT_SCHED_ID>104677</C_PAYMENT_SCHED_ID>
                            <C_AMT_DUE_REMAINING>9000</C_AMT_DUE_REMAINING>
                        </G_DETAIL>
                        <G_DETAIL>
                            <C_VOUCHER_NUMBER>23108</C_VOUCHER_NUMBER>
                            <C_INVOICE_TYPE>STANDARD</C_INVOICE_TYPE>
                            <C_DUE_DATE>29-AUG-17</C_DUE_DATE>
                            <C_INVOICE_NUMBER>testesc2</C_INVOICE_NUMBER>
                            <C_REFERENCE_NUMBER>1</C_REFERENCE_NUMBER>
                            <C_PAYMENT_SCHED_ID>118676</C_PAYMENT_SCHED_ID>
                            <C_AMT_DUE_REMAINING>3000000</C_AMT_DUE_REMAINING>
                        </G_DETAIL>
                        <G_DETAIL>
                            <C_VOUCHER_NUMBER>23109</C_VOUCHER_NUMBER>
                            <C_INVOICE_TYPE>STANDARD</C_INVOICE_TYPE>
                            <C_DUE_DATE>29-AUG-17</C_DUE_DATE>
                            <C_INVOICE_NUMBER>testesc6</C_INVOICE_NUMBER>
                            <C_REFERENCE_NUMBER>1</C_REFERENCE_NUMBER>
                            <C_PAYMENT_SCHED_ID>118677</C_PAYMENT_SCHED_ID>
                            <C_AMT_DUE_REMAINING>490000</C_AMT_DUE_REMAINING>
                        </G_DETAIL>
                    </LIST_G_DETAIL>
                    <C_SUM_V_DUE_REMAINING>7519000</C_SUM_V_DUE_REMAINING>
                    <C_SUM_V_DUE_REMAINING_F> 7,519,000.00 </C_SUM_V_DUE_REMAINING_F>
                    <C_SUM_V_INV_AMT_1>0</C_SUM_V_INV_AMT_1>
                    <C_SUM_V_INV_AMT_1_F> 0.00 </C_SUM_V_INV_AMT_1_F>
                    <C_SUM_V_INV_AMT_2>0</C_SUM_V_INV_AMT_2>
                    <C_SUM_V_INV_AMT_2_F> 0.00 </C_SUM_V_INV_AMT_2_F>
                    <C_SUM_V_INV_AMT_3>0</C_SUM_V_INV_AMT_3>
                    <C_SUM_V_INV_AMT_3_F> 0.00 </C_SUM_V_INV_AMT_3_F>
                    <C_SUM_V_INV_AMT_4>7519000</C_SUM_V_INV_AMT_4>
                    <C_SUM_V_INV_AMT_4_F> 7,519,000.00 </C_SUM_V_INV_AMT_4_F>
                    <C_PER_V_INV_AMT_1>0</C_PER_V_INV_AMT_1>
                    <C_PER_V_INV_AMT_2>0</C_PER_V_INV_AMT_2>
                    <C_PER_V_INV_AMT_3>0</C_PER_V_INV_AMT_3>
                    <C_SUM_V_DATA_CONVERTED>0</C_SUM_V_DATA_CONVERTED>
                    <C_PER_V_INV_AMT_4>100</C_PER_V_INV_AMT_4>
                    <C_V_DATA_CONVERTED>
                    </C_V_DATA_CONVERTED>
                </G_VENDOR>
            </LIST_G_VENDOR>
            <C_SUM_AMT_ORIGINAL>7520000</C_SUM_AMT_ORIGINAL>
            <C_SUM_AMT_REMAINING>7519000</C_SUM_AMT_REMAINING>
            <C_SUM_AMT_REMAINING_F> 7,519,000.00 </C_SUM_AMT_REMAINING_F>
            <C_SUM_INV_DUE_AMT_1>0</C_SUM_INV_DUE_AMT_1>
            <C_SUM_DATA_CONVERTED>0</C_SUM_DATA_CONVERTED>
            <C_PER_INV_DUE_AMT_1>0</C_PER_INV_DUE_AMT_1>
            <C_PGBRK_DATA_CONVERTED>
            </C_PGBRK_DATA_CONVERTED>
            <C_SUM_INV_DUE_AMT_1_F> 0.00 </C_SUM_INV_DUE_AMT_1_F>
            <C_SUM_INV_DUE_AMT_2>0</C_SUM_INV_DUE_AMT_2>
            <C_PER_INV_DUE_AMT_2>0</C_PER_INV_DUE_AMT_2>
            <C_SUM_INV_DUE_AMT_2_F> 0.00 </C_SUM_INV_DUE_AMT_2_F>
            <C_SUM_INV_DUE_AMT_3>0</C_SUM_INV_DUE_AMT_3>
            <C_PER_INV_DUE_AMT_3>0</C_PER_INV_DUE_AMT_3>
            <C_SUM_INV_DUE_AMT_3_F> 0.00 </C_SUM_INV_DUE_AMT_3_F>
            <C_SUM_INV_DUE_AMT_4>7519000</C_SUM_INV_DUE_AMT_4>
            <C_PER_INV_DUE_AMT_4>100</C_PER_INV_DUE_AMT_4>
            <C_SUM_INV_DUE_AMT_4_F> 7,519,000.00 </C_SUM_INV_DUE_AMT_4_F>
        </G_PRMBRK>
    </LIST_G_PRMBRK>
</APXINAGE>

Solution

  • Please modify the XSLT code as below. You need to access the master using ../../while looping through the detail.

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
        <xsl:output method="html" indent="yes" />
        <xsl:strip-space elements="*" />
    
        <xsl:template match="LIST_G_PRMBRK">
            <body>
                <h2>Vendor Details</h2>
                <table border="1">
                    <tr bgcolor="#9acd32">
                        <th>Vendor Name</th>
                        <th>Vendor Number</th>
                        <th>Voucher Number</th>
                        <th>Inv Type Number</th>
                        <th>Inv Number</th>
                    </tr>
                    <xsl:for-each select="G_PRMBRK/LIST_G_VENDOR/G_VENDOR/LIST_G_DETAIL/G_DETAIL">
                        <tr>
                            <td><xsl:value-of select="../../C_VENDOR_NAME" /></td>
                            <td><xsl:value-of select="../../C_VENDOR_NUMBER" /></td>
                            <td><xsl:value-of select="C_VOUCHER_NUMBER" /></td>
                            <td><xsl:value-of select="C_INVOICE_TYPE" /></td>
                            <td><xsl:value-of select="C_INVOICE_NUMBER" /></td>
                        </tr>
                    </xsl:for-each>
                </table>
            </body>
        </xsl:template>
    </xsl:stylesheet>
    

    Output

    <body>
        <h2>Vendor Details</h2>
        <table border="1">
            <tr bgcolor="#9acd32">
                <th>Vendor Name</th>
                <th>Vendor Number</th>
                <th>Voucher Number</th>
                <th>Inv Type Number</th>
                <th>Inv Number</th>
            </tr>
            <tr>
                <td>3 PL SOL</td>
                <td>10000012</td>
                <td>23103</td>
                <td>STANDARD</td>
                <td>test2</td>
            </tr>
            <tr>
                <td>7 STAR</td>
                <td>10000009</td>
                <td>1177</td>
                <td>STANDARD</td>
                <td>test</td>
            </tr>
            <tr>
                <td>7 STAR</td>
                <td>10000009</td>
                <td>23079</td>
                <td>STANDARD</td>
                <td>Test3</td>
            </tr>
            <tr>
                <td>7 STAR</td>
                <td>10000009</td>
                <td>23108</td>
                <td>STANDARD</td>
                <td>testesc2</td>
            </tr>
            <tr>
                <td>7 STAR</td>
                <td>10000009</td>
                <td>23109</td>
                <td>STANDARD</td>
                <td>testesc6</td>
            </tr>
        </table>
    </body>