Skip to Content
avatar image
Former Member

Mapping multiple fields from xml file to target database structure

I have weird scenario, where I need to map the xml file to the target database structure. I am using sftp-to-jdbc adapter to achieve it. How should I map these fields? But, the complexity is all about the date fields.. The source xml structure looks like below:

<Record>

<map id>8439357</map id>

<duration>3 months</duration>

<name>john</john>

<2017-01-22></2017-01-22>

<2017-01-23></2017-01-23>

<2017-01-24></2017-01-24>

<2017-01-25>Attended</2017-01-25>

<2017-01-26>Not Attended</2017-01-26>

<2017-01-27></2017-01-27>

<2017-01-26></2017-01-26>

<2017-01-27></2017-01-27>

</Record>

Here in the above structure, the date fields are multiple. Not sure how many fields it comes in each xml file. Its unique. Target Database Table as below:

Table COLUMN:

  1. map id
  2. duration name
  3. Attendance Date
  4. Attendance Value

So, when each and every date will be into the "Attendance Date" field in Database Table and the correspondence Value will be in the "Attendance Value"

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 23, 2017 at 07:10 AM

    Hi,

    Few points -

    1) Your source XML nodes are not fixed. Hence, there is no way that you can create one DT for the source XML. So, only option is to use JAVA mapping and reading node by node after node <name>.

    2) How can you insert multiple attendance date and value into the mentioned table column? If there is only one column for attendance date then you can insert only one date for each row. So either you can have 31 attendance date and value column in the table or you need you need to create one row for each attendance date. Clarify this point.

    Thanks,

    Apu

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks for your information!

      1. I agree with you that, I will create a JAVA mapping for reading the data in the XML file, here the date fields are not fixed so I will read the data after the node <name>. But here we have to put the date field in Attendance date and value of the same in the Attendance Value.

      As you know, the target structure is database structure. So when doing the JAVA mapping, how we need to insert into this structure?

      How do I map the normal fields using JAVA mapping, apart from the date fields to target fields?

      Could you please help me?

      2. We are going to insert multiple "Attendance Date" in row by row in Database table and values of that accordingly row by row in "Attendance Value".

  • May 23, 2017 at 08:34 AM

    Hi Ram,

    I have another approach, not sure work for you or not but you can try

    create a data type as below:

    <Record>

    <map id>8439357</map id>

    <duration>3 months</duration>

    <name>john</name>

    </Record>

    and pass the dates values as it is but dont validate the XML message , and use UDF to read the values and take the input as string using "return as XML" and create date accordingly.

    Regards

    GB

    Add comment
    10|10000 characters needed characters exceeded