Mapping multiple fields from xml file to target database structure

[Former Member]

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"

[gagandeep_batra]

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

[gagandeep_batra]

I just did the small thing for your reference but you can extend this according to your need

my input is like that

<row>
<firstName>gag</firstName>
<lastName>bb</lastName>
<a2017-01-22>np</a2017-01-22>
<a2017-01-23>ok</a2017-01-23>
</row>

and code its

int startindex=var1[0].indexOf("</lastName>");

startindex=startindex+11;
String tagvalue="";
String tagname=var1[0].substring(startindex+2,startindex+12);

ADate1.addValue(tagname);
------------------------------

Regards

GB

[Former Member]

Hi Gangandeep,

Thanks for your detailed screenshots and information in it!

I tried to replicate the same udf, but getting error. Attached here is xml data, mapping and udf code.

[gagandeep_batra]

Hi

Did you pass input as string using "return as XML"

Regards

GB

[Former Member]

Yes Gagan, I have kept "return as XML", but also same error.

[apu_das2]

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

[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".