Skip navigation
Contact Us
3770 Views 9 Replies Latest reply: Jan 5, 2013 5:03 PM by Anand p RSS
Sailendra Behera Novice 24 posts since
Aug 9, 2011
Currently Being Moderated

Jun 21, 2012 7:54 AM

How to generate multiple xml target files based on row count?

Hi

 

Please help me on the following problem.

 

I have a mapping whose source is Oracle and I need to have XML as target files. The problem is that I need to have multiple xml files on the target directory based on the no of rows coming from the source DB.

 

Let's say I have x lakh records coming from the source I need to have a mapping which will divide the records into n number of target XML files. As size of the XML file is being an issue I have to create a XML file with 20000 recs only.

 

 

I have created a mapping with this following sequence

 

SQ--->LKps(2)---->ExpTrans--->Router--->xmlTarget

 

 

Please help me out on this...

  • Veeru B Master 1,242 posts since
    Sep 27, 2010

    Hi Sailendra,

     

    Try with transaction control transformation.

     

    Regards,

    Veeru

  • karthik RP Novice 24 posts since
    Jun 13, 2011

    Hi,

     

    I have implemented the below approach in my current project and this is working perfectly since last year. Please find the steps below for your ref.

    Add an expression transformation and aggregator transformation before the target xml as shown below. There are 2 scenarios in this case based on Root Element referential Integrity in the Target xml file structure.

     

     

    Scenario1: If the target xml has simple hierarchy and the root element has not been used as a FK in the underlying child hierarchies then it is enough we have single expression transformation and aggregator transformation before target

     

     

     

     

    Scenario2:

     

     

     

    If the target xml has Complex hierarchy and the root element has been used as a FK for its child hierarchies then we need to have multiple expression transformation and aggregator transformations based on hierarchy.

    In the below case the root element XPK_ENTRIES is being referred by its child element Organization.

    So we have used 2 sets of expression transformation and aggregator transformations

    

    DUMMY : A port used as a dummy to get the incoming source records.

     

     

    3 variable ports:

     

     

     

     

    1) v_FILE_SPLIT_COUNT= :LKP.lkp_File_Split_Record_Count

    ('FILE_SPLIT_COUNT-CUSTOMER_MASTER')

     

    (Note:This is a hardcoded value ('FILE_SPLIT_COUNT-CUSTOMER_MASTER) entered ina reference table. Do not get confused with this, since we follow a ref table to hold hardcode entries rather specifying in code level

     

    2) v_ROOT = IIF(v_FILE_SPLIT_COUNT=0,0,IIF(V_RECORD_COUNT=v_FILE_SPLIT_COUNT, v_ROOT+1,v_ROOT))

    3) v_RECORD_COUNT = IIF(v_FILE_SPLIT_COUNT=0,0,IIF (V_RECORD_COUNT=v_FILE_SPLIT_COUNT,1, V_RECORD_COUNT+1))

    4) o_XPK_ENTRIES =v_ROOT   ---output port

     

    #

     

    Step 5:

     

    Map the o_XPK_ENTRIES port from expression transformation to the aggregator as shown below and select the XPK_ENTRIES port as group by port in the aggregator.

     

     

    I have prepared a technical documentation stating the steps in detail, if needed i can even share with you guys.

     

    Thanks fro your time.

    

    Regards

    KarthikRP

     

     

    .

More Like This

  • Retrieving data ...

Bookmarked By (1)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points