topic Re: dynamic xml conversion in Talend Studio

dynamic xml conversion

Anonymous — Sat, 18 Jun 2016 18:06:43 GMT

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

Re: dynamic xml conversion

Anonymous — Sat, 18 Jun 2016 18:14:55 GMT

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.

Re: dynamic xml conversion

Anonymous — Sat, 18 Jun 2016 18:19:02 GMT

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

Re: dynamic xml conversion

Anonymous — Sat, 18 Jun 2016 19:33:38 GMT

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.

Re: dynamic xml conversion

Anonymous — Sun, 19 Jun 2016 03:12:54 GMT

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.

Re: dynamic xml conversion

Anonymous — Sun, 19 Jun 2016 16:34:02 GMT

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);