Mapping : Complex Structured Source to JDBC receiver structure

[former_member187339]

Hello...

We have a source structure as

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MTO_Corporate xmlns:ns0="http://axis.com/authorization">
   <Details>
      <Corporate_Details> ---------- (occurence 1 to n)
         <ID/>
         <Name/>
         <Address/>
         <Delivery_Address/>
         <Email_ID/>
         <Request_Limit/>
         <Total_Limit/>
         <Used_Limit/>
         <Availability_Limit/>
         <Sanction_Date/>
         <Created_By/>
         <Created_Date/>
         <Fax_No/>
         <Max_Authorizers/>
         <Account_Details> ---------- (occurence 1 to m)
            <Corp_Acc_Type/>
            <Corp_Acc_Num/>
            <Branch_Code/>
            <Branch_Name/>
            <Bank_City/>
         </Account_Details>
         <Department_Details> ---------- (occurence 0 to x)
            <Dept_Code/>
            <Dept_Name/>
         </Department_Details>
         <Designation_Details>---------- (occurence 0 to y)
            <Desig_Code/>
            <Desig_Name/>
         </Designation_Details>
         <Status/>
         <State/>
      </Corporate_Details>
   </Details>
</ns0:MTO_Corporate>

In this structure, each corporate can have more than one accounts and departments and designations. We need to map this with the following target structure:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MTI_Corporate_DB xmlns:ns0="http://axis.com/authorization">

<StatementCorporate>

<AXIS_STORED_PROCEDURE action="EXECUTE">

<Table>SP_CORPORATE_MASTER_INSERT</Table>

<f_corp_code isInput="true"></f_corp_code>

<f_corp_name isInput="true"></f_corp_name>

<f_corp_addr1 isInput="true"></f_corp_addr1>

<f_corp_addr2 isInput="true"></f_corp_addr2>

<f_primary_email isInput="true"></f_primary_email>

<f_corp_req_limit isInput="true"></f_corp_req_limit>

<f_corp_total_limit isInput="true"></f_corp_total_limit>

<f_corp_upper_limit isInput="true"></f_corp_upper_limit>

<f_corp_avail_limit isInput="true"></f_corp_avail_limit>

<f_effective_from isInput="true"></f_effective_from>

<f_created_by isInput="true"></f_created_by>

<f_created_date isInput="true"></f_created_date>

<f_corp_fax isInput="true"></f_corp_fax>

<f_Max_Authorizers isInput="true"></f_Max_Authorizers>

<f_corp_acc_type isInput="true"></f_corp_acc_type>

<f_corp_acc_num isInput="true"></f_corp_acc_num>

<f_corp_branch_code isInput="true"></f_corp_branch_code>

<f_corp_branch_name isInput="true"></f_corp_branch_name>

<f_corp_bank_city isInput="true"></f_corp_bank_city>

<f_corp_dept_code isInput="true"></f_corp_dept_code>

<f_corp_dept_name isInput="true"></f_corp_dept_name>

<f_corp_desig_code type="true"></f_corp_desig_code>

<f_corp_desig_name isInput="true"></f_corp_desig_name>

<f_corp_enable isInput="true"></f_corp_enable>

<f_corp_status isInput="true"></f_corp_status>

<p_err_msg isOutput="true" type="VARCHAR">

</p_err_msg><p_status isOutput="true" ype="VARCHAR">

</p_status>

</AXIS_STORED_PROCEDURE>

</StatementCorporate>

</ns0:MTI_Corporate_DB>

Yes, we are using a stored procedure which will insert 5 tables. The problem is in the mapping between souce and target fields.

Plese provide your inputs on this query.

Regards

Suraj

[Former Member · Accepted solution]

Hi,

U need to create number of target message as that of number of account details so that it will be one query for ur database.

So map ur source <account_details> node with target root node and change context of <account_detail> to <details>..these will create number of target messages.

Now map ur target node with account, dept, and designation details by changing it context to root node.

Regards,

Manisha.

[former_member187339 · Accepted solution]

Hi Manisha,

>>U need to create number of target message as that of number of account details so that it will be one query for ur database.

Yes this could have been done if we were sure that a corporate will have more accounts than departments/designations... But that is not always the case.

>>So map ur source <account_details> node with target root node and change context of <account_detail> to <details>..these will create number of target messages.

So what I did is to check the larger of account, department and designation and then repeat the traget strucutre that much times...

>>Now map ur target node with account, dept, and designation details by changing it context to root node

But still I have some elements like ID, name etc.which comes one for each corporate and they also needs to repeated.. I am thinking that this will not be an easy solution.

Anyway thanks for the reply and please keep updated if you have any other idea.

Regards

Suraj

[Former Member · Accepted solution]

Things which are common and not repeated..do these for them

source field --- CopyValue(in constant option) --- target field

just try with this...it may work

Regards,

Manisha

[former_member187339 · Accepted solution]

Hi,

I tried that one too..but still problme exists...

if i have two corporate ids... always i am getting the vale of the first corporate in the target structure. I tried changing contexts too but still the problem exists.

Regards

Suraj

[Former Member · Accepted solution]

In ur source message how many cooperate ids u r getting? is that message is for only one cooperate details?

Regards,

Manisha.

[former_member187339 · Accepted solution]

Hi,

>>In ur source message how many cooperate ids u r getting? is that message is for only one cooperate details?

Yes there can be multiple corporates and target should be made for each one of them..

Say i have 2 corporates

Corp1 having account details 3 times, depart 2 times and desig 1 times

Corp2 having account details 2 times, depart 4 times and desig 3 times

then the output should be repeated 7 times (3 times for CORP1 since it is having 3 account details and 4 for CORP2 since it has 4 Department details)

Regards

Suraj

[former_member187339 · Accepted solution]

Hi,

The problem is soleved. I wrote a small udf.

Thanks

Suraj

[Former Member · Accepted solution]

Hi,

I have a similar scenario can suggest what u did to resolve the issue.