topic Re: Problem converting XML with multiple loop in Talend Studio

Problem converting XML with multiple loop

Anonymous — Mon, 07 May 2012 06:54:47 GMT

Hello,
I have problems with converting one XML to another with difference structure.
If you know LCI data format, im converting difference data format (from Ecospold to ILCD) which is the two of them using XML as data format.
here what is look like in simple way:
from this:
<processDataSet xmlns=" http://lca.jrc.it/ILCD/Process" xmlns:common=" http://lca.jrc.it/ILCD/Common" xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" metaDataOnly="false" locations="../ILCDLocations.xml" version="1.1" xsi:schemaLocation=" http://lca.jrc.it/ILCD/Process ../../schemas/ILCD_ProcessDataSet.xsd">
<processInformation>
<dataSetInformation> <common:UUID>9ca9d601-15b7-45c3-84ad-e46f9a165809</common:UUID> <nam

To see the whole post, download it here

Re: Problem converting XML with multiple loop

Anonymous — Mon, 07 May 2012 07:56:50 GMT

Hi Rocknrolla
I try to reproduce the job you mentioned above.
But I find that the format of these two xml files are not correct.
Could you simplify the structure of them?
Did you check "Create empty element if needed" in tFileOutputMSXml->Advanced Settings?
Moreover, since TOS 5.1.0, tXMLMap supports multiple loop feature.
Regards,
Pedro

Re: Problem converting XML with multiple loop

Anonymous — Mon, 07 May 2012 09:52:05 GMT

Hi Rocknrolla
I try to reproduce the job you mentioned above.
But I find that the format of these two xml files are not correct.
Could you simplify the structure of them?
Did you check "Create empty element if needed" in tFileOutputMSXml->Advanced Settings?
Moreover, since TOS 5.1.0, tXMLMap supports multiple loop feature.
Regards,
Pedro

hello, thx for the quick respond
Its not correct because it is not the actual file, edit: i cant upload it because it is too long and the forum not allowing me to post it all
i check the tFileOutputMSXml component file, edit: and i already check create empty element if needed but the empty element from the imported xml structure wont showed up
and how to use tXMLMap to use multiple loop? and can it connected with file output XML with the same structure, i mean the XML tree in the file output is difference right?
i use TOS 5.0.2 i think i should upgrade it

Re: Problem converting XML with multiple loop

Anonymous — Mon, 07 May 2012 09:59:45 GMT

Hi
Could you send me an email and attach these two xml?
Because without reproducing this issue, I can not point out how to fix it.
Regards,
Pedro

Re: Problem converting XML with multiple loop

Anonymous — Mon, 07 May 2012 10:25:52 GMT

Hi
Could you send me an email and attach these two xml?
Because without reproducing this issue, I can not point out how to fix it.
Regards,
Pedro

i will email it
also can you explain how to use tXMLmap+tAdvanceFileOutputXML combined?

thanks

Re: Problem converting XML with multiple loop

Anonymous — Mon, 07 May 2012 11:28:59 GMT

Hi
When using tFileOutputMSXML, if an attribute gets null values, this attribute will disappear in output XML file.
It seems to be a bug. Please report it on BugTracker.
When using tXMLMap, it works fine.
So now the workaround is to use tXMLMap.
Just link tXMLMap with tFileOutputDelimited which has only one 'Document' column.
The file name of tFileOutputDelimited should be *.xml.
Regards,
Pedro

Re: Problem converting XML with multiple loop

Anonymous — Mon, 07 May 2012 16:18:55 GMT

Hi
When using tFileOutputMSXML, if an attribute gets null values, this attribute will disappear in output XML file.
It seems to be a bug. Please report it on BugTracker.
When using tXMLMap, it works fine.
So now the workaround is to use tXMLMap.
Just link tXMLMap with tFileOutputDelimited which has only one 'Document' column.
The file name of tFileOutputDelimited should be *.xml.
Regards,
Pedro

i had use your methods, at first it is okay. Little mess at the structure but is is not a problem, i can produce null attribute. But, when i apply some loop, the problem start
the looping is applied on whole XML, so if there is next loop applied the whole XML is written and so on.

Re: Problem converting XML with multiple loop

Anonymous — Tue, 08 May 2012 05:51:00 GMT

Hi
The "all in one" feature can be found in tXmlMap Settings which supports generating multi rows into one xml row as the following image.
Regards,
Pedro

Re: Problem converting XML with multiple loop

Anonymous — Tue, 08 May 2012 06:23:49 GMT

Hi
The "all in one" feature can be found in tXmlMap Settings which supports generating multi rows into one xml row as the following image.
Regards,
Pedro

its working now
thanks a lot

Re: Problem converting XML with multiple loop

Anonymous — Tue, 08 May 2012 12:09:33 GMT

Hi
The "all in one" feature can be found in tXmlMap Settings which supports generating multi rows into one xml row as the following image.
Regards,
Pedro

Hello,
it turns out my problem is not solve yet
The xml is looping, the file is produce all is good, but the my main problems which is multiple loop still cant be done. i tried with multiple loop using TOS 5.1 as you suggested but when i applied it, it produce nothing.
for your information, i separate the input from same XML files and combined it later in the XMLmap. are my method is incorrect?
as you can see on the picture below, i separate the file and it produce 73 rows in elecNSW_3 and 4 rows in elecNSW_4. but only 1 row in the output.

Re: Problem converting XML with multiple loop

Anonymous — Tue, 08 May 2012 13:23:15 GMT

Hi,
Another approach is to use a set of tAdvancedFileOutputXML components in append mode. Take a look at the "Multiple Loops" section of this post.
You may also find it easier to integrate the source data into an RDBMS (including ones like MS Access) and write a denormalized query that will be the basis for your XML generation.
http://bekwam.blogspot.com/2011/09/xml-output-from-multiple-data-sources.html

Re: Problem converting XML with multiple loop

Anonymous — Tue, 08 May 2012 15:12:54 GMT

Hi,
Another approach is to use a set of tAdvancedFileOutputXML components in append mode. Take a look at the "Multiple Loops" section of this post.
You may also find it easier to integrate the source data into an RDBMS (including ones like MS Access) and write a denormalized query that will be the basis for your XML generation.
http://bekwam.blogspot.com/2011/09/xml-output-from-multiple-data-sources.html

hi, thanks for responding to my problem
i look at your blog and i dont quite understand how append mode works. is it we must put relationship between difference loop or we can simply add row with difference row after we finish previous subjob?
in my case i tried to loop the element that need loop one after another, and it didnt work
the error was like this:
org.dom4j.DocumentException: Error on line 3 of document file:///I:/SGU%20Material/Semester%208/Thesis/ETL%20Tool/LCI%20Data/appendxmltest.xml : The prefix "xsi" for attribute "xsi:schemaLocation" associated with an element type "ecoSpold" is not bound. Nested exception: The prefix "xsi" for attribute "xsi:schemaLocation" associated with an element type "ecoSpold" is not bound.
at org.dom4j.io.SAXReader.read(SAXReader.java:482)
at org.dom4j.io.SAXReader.read(SAXReader.java:264)
at lcidataconverterprototype.appendxml_0_1.AppendXML.tFileInputXML_2Process(AppendXML.java:2312)
at lcidataconverterprototype.appendxml_0_1.AppendXML.tFileInputXML_1Process(AppendXML.java:1797)
at lcidataconverterprototype.appendxml_0_1.AppendXML.runJobInTOS(AppendXML.java:3833)
at lcidataconverterprototype.appendxml_0_1.AppendXML.main(AppendXML.java:3701)
Nested exception:
org.xml.sax.SAXParseException: The prefix "xsi" for attribute "xsi:schemaLocation" associated with an element type "ecoSpold" is not bound.
at org.apache.xerces.util.ErrorHandlerWrapper.createSAXParseException(Unknown Source)
at org.apache.xerces.util.ErrorHandlerWrapper.fatalError(Unknown Source)
at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)
at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)
at org.apache.xerces.impl.XMLNSDocumentScannerImpl.scanStartElement(Unknown Source)
at org.apache.xerces.impl.XMLNSDocumentScannerImpl$NSContentDispatcher.scanRootElementHook(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
at org.dom4j.io.SAXReader.read(SAXReader.java:465)
at org.dom4j.io.SAXReader.read(SAXReader.java:264)
at lcidataconverterprototype.appendxml_0_1.AppendXML.tFileInputXML_2Process(AppendXML.java:2312)
at lcidataconverterprototype.appendxml_0_1.AppendXML.tFileInputXML_1Process(AppendXML.java:1797)
at lcidataconverterprototype.appendxml_0_1.AppendXML.runJobInTOS(AppendXML.java:3833)
at lcidataconverterprototype.appendxml_0_1.AppendXML.main(AppendXML.java:3701)
Nested exception: org.xml.sax.SAXParseException: The prefix "xsi" for attribute "xsi:schemaLocation" associated with an element type "ecoSpold" is not bound.
at org.apache.xerces.util.ErrorHandlerWrapper.createSAXParseException(Unknown Source)
at org.apache.xerces.util.ErrorHandlerWrapper.fatalError(Unknown Source)
at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)
at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)
at org.apache.xerces.impl.XMLNSDocumentScannerImpl.scanStartElement(Unknown Source)
at org.apache.xerces.impl.XMLNSDocumentScannerImpl$NSContentDispatcher.scanRootElementHook(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
at org.dom4j.io.SAXReader.read(SAXReader.java:465)
at org.dom4j.io.SAXReader.read(SAXReader.java:264)
at lcidataconverterprototype.appendxml_0_1.AppendXML.tFileInputXML_2Process(AppendXML.java:2312)
at lcidataconverterprototype.appendxml_0_1.AppendXML.tFileInputXML_1Process(AppendXML.java:1797)
at lcidataconverterprototype.appendxml_0_1.AppendXML.runJobInTOS(AppendXML.java:3833)
at lcidataconverterprototype.appendxml_0_1.AppendXML.main(AppendXML.java:3701)
Exception in component tAdvancedFileOutputXML_2
java.lang.Exception: can not find the file:I:/SGU Material/Semester 8/Thesis/ETL Tool/LCI Data/appendxmltest.xml
at lcidataconverterprototype.appendxml_0_1.AppendXML.tFileInputXML_2Process(AppendXML.java:2318)
at lcidataconverterprototype.appendxml_0_1.AppendXML.tFileInputXML_1Process(AppendXML.java:1797)
at lcidataconverterprototype.appendxml_0_1.AppendXML.runJobInTOS(AppendXML.java:3833)
at lcidataconverterprototype.appendxml_0_1.AppendXML.main(AppendXML.java:3701)
i dont quite understand because im new on talend

Re: Problem converting XML with multiple loop

Anonymous — Wed, 09 May 2012 10:26:17 GMT

Hi Rocknrolla
I encounter an interesting thing that when I create this job by using tFileOutputMSXML, it works fine this time. The attributes with null values don't disappear this time.
Could you tell me the steps to reproduce this issue you mentioned at Comment #1?
Regards,
Pedro

Re: Problem converting XML with multiple loop

Anonymous — Thu, 10 May 2012 09:31:16 GMT

Hi Rocknrolla
I encounter an interesting thing that when I create this job by using tFileOutputMSXML, it works fine this time. The attributes with null values don't disappear this time.
Could you tell me the steps to reproduce this issue you mentioned at Comment #1?
Regards,
Pedro

Sure,
1. i divide the xml file by creating 4 different inputXML (in this step i convert AUSLCI_Elec_Beta0_9BCPowerQLD.XML to 9ca9d601-15b7-45c3-84ad-e46f9a165809_01.00.000.xml) each inputXML serve for 4 different element which are, processinformation, modelling and validation, administrativeinformation and exchange)
2. then, i assign each input XML into tMapComponent which i mapped every metadata to another (some with modification
3. last, i assign all of this into tFileOutputMSXML
4. in tFileOutputMSXML, i import XML tree from 9ca9d601-15b7-45c3-84ad-e46f9a165809_01.00.000.xml, all for four of them (tFileOutputMSXML ask for four XML tree). but, i only using the element that the XML assign for, means i assign the previous mapped input for exchange into imported XML tree with only exchange element and deleted the rest. i only keep several attribute outside that four elements
ps: in the advance setting i check the option for create directory if not exists, create empty element if needed, trim the whitespace charactes and escape test

Re: Problem converting XML with multiple loop

Anonymous — Tue, 15 May 2012 13:51:11 GMT

hello pedro,
have you found the problem yet?