DB Adapter output XML repeats records in the child collection (Doc ID 2000033.1)

Last updated on APRIL 28, 2015

Applies to:

Oracle SOA Suite - Version 11.1.1.7.0 and later
Information in this document applies to any platform.

Symptoms

Using the DB Adapter to query a parent and child table.  The child table can have n records for each parent and the output XML is expected to contain the collection of parent records, each having the associated collection of child records embedded.

Issue is that the output XML is repeating the first child record n times for each parent record instead of showing each record.  So if there are 10 child records for a given parent, the output XML contains the first child record repeated 10 times.

DB Adapter configured with the parent and child tables imported, the parent selected as the root an the 1:M relationship.  Default generated XML is used and the results of the adapter invoke are viewed through the EM instance audit trail.

 

Example Expected Output:

<Invoke1_dbReferenceSelect_OutputVariable>
<part xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="TestParentCollection">
<TestParentCollection xmlns="http://xmlns.oracle.com/pcbpel/adapter/db/top/dbReference">
    <TestParent>
        <column1>1234</column1>
        <column2>Parent1</column2>
        <column3>ParentRow1</column3>
        <testChildCollection>
            <TestChild>
                <column1>1</column1>
                <column3>Row1</column3>
            </TestChild>
            <TestChild>
                <column1>2</column1>
                <column3>Row2</column3>
            </TestChild>
            <TestChild>
                <column1>3</column1>
                <column3>Row3</column3>
            </TestChild>
        </testChildCollection>
    </TestParent>
</TestParentCollection>
</part>
</Invoke1_dbReferenceSelect_OutputVariable>

 

Example of Incorrect Output:

<Invoke1_dbReferenceSelect_OutputVariable>
<part xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="TestParentCollection">
<TestParentCollection xmlns="http://xmlns.oracle.com/pcbpel/adapter/db/top/dbReference">
    <TestParent>
        <column1>1234</column1>
        <column2>Parent1</column2>
        <column3>ParentRow1</column3>
        <testChildCollection>
            <TestChild>
                <column1>1</column1>
                <column3>Row1</column3>
            </TestChild>
            <TestChild>
                <column1>1</column1>
                <column3>Row1</column3>
            </TestChild>
            <TestChild>
                <column1>1</column1>
                <column3>Row1</column3>
            </TestChild>
        </testChildCollection>
    </TestParent>
</TestParentCollection>
</part>
</Invoke1_dbReferenceSelect_OutputVariable>

 

 

JDBC debug output in WebLogic shows that the correct records are returned from the DB so the issue appears to be in the XML mapping.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms