dynamic xml conversion

Four Stars

dynamic xml conversion

Hi,
I have a scenario like below
Source - Not sure about columns/structure. one day source file come with 100 fields, next day it can be 110, another day 90. Source file with delimiter.
Target - We want to convert it into xml file.
Please provide any solutions.
Thanks
Jay

Ten Stars

Re: dynamic xml conversion

If you don't know the schema, how do you know the data types for your input files? Is there some method to understand the data (known header names)?

Also, what structure are you looking to produce XML in? I am assuming it will have be a single level of elements named according to the column names.

If you know all of the possible column names and the xml is a single level, you can do this. But it will be a mess tXMLMap.
Four Stars

Re: dynamic xml conversion

Yes, we will have column names as header/first row in the file.
Can you explain the solution clearly
Ten Stars

Re: dynamic xml conversion

You will need all possible column names for all files at design time. If you have that, then this tutorial I put together on something similar might help (https://www.rilhia.com/tutorials/dynamic-column-order). But it will be messy.

For the XML schema, you will need to have every column preconfigured. Then in the tXMLMap, set up the "create empty element" setting for the XML output to be false.
Four Stars

Re: dynamic xml conversion

Hi,
Thanks for providing solutions here.
But we don't know the possible column names.  The list can be changed any time. We don't have max columns list.
New fields will be added and/or existing fields will be removed in the next file.
Is there any solution for this kind of scenario?
Thanks in advance.
Ten Stars

Re: dynamic xml conversion

OK, this is a highly unusual scenario but it tempted me to try and solve it. I have a solution. But it requires Java and is not elegant. You will need to build upon this.
For this you will need to know the maximum number of columns you might get in a file, that is all.
This is built using your source data (assuming that the first row are the column headers) and a tJavaFlex. We build the XML in the tJavaFlex and print it out in the End Code section. You can also set it to a globalMap variable and then convert it to an actual Document later if you want. I haven't covered that. I am assuming in this example that there will be columns without data and that they should not be used in the XML. 

Below are the Start Code, Main Code and End Code sections for the tJavaFlex I used. You will need to change this to suit your job.
Start Code
// Create a rowcount variable to keep track of where we are
int rowcount = 0;

//Create a hashmap to keep the first row values (for column names) assigned to
//an integer to represent their column number
java.util.HashMap<Integer, String> columnHeader = new java.util.HashMap<Integer, String>();

//Create the opening XML element.
String XML = "<myXML>";



Main Code
//For row 0 create the header information
if(rowcount==0){
columnHeader.put(1,row1.newColumn);
columnHeader.put(2,row1.newColumn1);
columnHeader.put(3,row1.newColumn2);
columnHeader.put(4,row1.newColumn3);
columnHeader.put(5,row1.newColumn4);
columnHeader.put(6,row1.newColumn5);
columnHeader.put(7,row1.newColumn6);
columnHeader.put(8,row1.newColumn7);
columnHeader.put(9,row1.newColumn8);

}else{ //for every other row, check to see if the header is not null and then
//build an element for it if it is not empty.

XML = XML+"<loop>\n";

if(columnHeader.get(1)!=null){
XML = XML+"<"+columnHeader.get(1)+">"+row1.newColumn+"</"+columnHeader.get(1)+">\n";
}
if(columnHeader.get(2)!=null){
XML = XML+"<"+columnHeader.get(2)+">"+row1.newColumn1+"</"+columnHeader.get(2)+">\n";
}
if(columnHeader.get(3)!=null){
XML = XML+"<"+columnHeader.get(3)+">"+row1.newColumn2+"</"+columnHeader.get(3)+">\n";
}
if(columnHeader.get(4)!=null){
XML = XML+"<"+columnHeader.get(4)+">"+row1.newColumn3+"</"+columnHeader.get(4)+">\n";
}
if(columnHeader.get(5)!=null){
XML = XML+"<"+columnHeader.get(5)+">"+row1.newColumn4+"</"+columnHeader.get(5)+">\n";
}
if(columnHeader.get(6)!=null){
XML = XML+"<"+columnHeader.get(6)+">"+row1.newColumn5+"</"+columnHeader.get(6)+">\n";
}
if(columnHeader.get(7)!=null){
XML = XML+"<"+columnHeader.get(7)+">"+row1.newColumn6+"</"+columnHeader.get(7)+">\n";
}
if(columnHeader.get(8)!=null){
XML = XML+"<"+columnHeader.get(8)+">"+row1.newColumn7+"</"+columnHeader.get(8)+">\n";
}
if(columnHeader.get(9)!=null){
XML = XML+"<"+columnHeader.get(9)+">"+row1.newColumn8+"</"+columnHeader.get(9)+">\n";
}


XML = XML+"</loop>\n";
}

//append 1 to the rowcount
rowcount++;


End Code
// close the XML opening tag

XML = XML + "</myXML>";

System.out.println(XML);

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download