Jun 21, 2012 7:54 AM

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.

If you mark on the XML target it will be having a primary key atleast. If you provide unique values to it through a sequence generator it will generate equal no of XML files. If you don't provide unique values it will definitely create the XML target file but the session log will contains error like Rejected record due to unique constraint violation.

What I done to that, I generated a xml file against each record and with a unix script I combine the XMLs at particular intervals I mean combining constant no of xml files to make it one.

I am not sure of Transaction Control as I didn't go with that. If you find that please let me know as well.

I was able to split the target XML files based on source row count. I used the target property "On commit" and set it to "Create new Document" in the target definition and then used the "Commit Type" in the session as "Source". Also unckecked "Commit on End of File". This will create new files based on the "Commit Interval".

For example: if the source records are 1000 and commit interval is 100, with the above configuration, 10 different xml files will be created.

There is no need of Transaction control for this as its working for me without it.

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