Convert a Delimited File to XML output with an XSD

One Star

Convert a Delimited File to XML output with an XSD

Hello,
I am new to Talend so apologies for what is probably a very simple job - I just can't seem to figure it out. In my example I have a "complex" delimited file which is as follows:
O|1234|John Smith|Ola Nordmann|Langgt 23|4000 Stavanger|Norway
I|1234|Empire Burlesque|1|10.90|Special Edition
I|1234|Hide your heart|1|9.90

Which is structured as follows:
recordType|orderId|OrderPerson|ShipToName|Address1|Address2|Address3
recordType|orderId|title|quanity|price|notes

Therefore I have created a tFileInputMSDelimited component to process the input file which generates two outputs "row_A_1" (the order) and "row_B_1" (the items).
I want to now convert this file to XML, I have the following XSD:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="shiporder">
<xs:complexType>
<xs:sequence>
<xs:element name="orderperson" type="xs:string"/>
<xs:element name="shipto">
<xs:complexType>
<xs:sequence>
<xs:element name="name" type="xs:string"/>
<xs:element name="address" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="country" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="item" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="title" type="xs:string"/>
<xs:element name="note" type="xs:string" minOccurs="0"/>
<xs:element name="quantity" type="xs:positiveInteger"/>
<xs:element name="price" type="xs:decimal"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="orderid" type="xs:string" use="required"/>
</xs:complexType>
</xs:element>
</xs:schema>

Therefore I have created a tFileOutputMSXML component and Imported the XSD document as the XML Tree.
How do I get the two different output rows to map to the XML and produce the output?
Any assistance would be greatly appreciated - I'm losing what little hair I have left!!
Cheers
Andy
Community Manager

Re: Convert a Delimited File to XML output with an XSD

Hello
1)As you have done, using tFileInputMSDelimited component to process the input file which generates two outputs "row_A_1" (the order) and "row_B_1" (the items), and store them into memory.
2)Read them from memory and do a inner join on tMap, concatenate all the fileds on output table.
3)Define the xml tree manually based on your xsd file.
in.csv:

O|1234|John Smith|Ola Nordmann|Langgt 23|4000 Stavanger|Norway
I|1234|Empire Burlesque|1|10.90|Special Edition
I|1234|Hide your heart|1|9.90
O|1235|shong|talend|Langgt 24|5000 Stavanger|Norway1
I|1235|Empire Burlesque|1|11.20|Special Edition
I|1235|Hide your heart|1|19.90|note2

out.xml:

<?xml version="1.0" encoding="ISO-8859-15"?>
<root>
<shiporder orderid="1234">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city/>
<country/>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>10.9</price>
</item>
</shiporder>
<shiporder orderid="1234">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city/>
<country/>
</shipto>
<item>
<title>Hide your heart</title>
<note></note>
<quantity>1</quantity>
<price>9.9</price>
</item>
</shiporder>
<shiporder orderid="1235">
<orderperson>shong</orderperson>
<shipto>
<name>talend</name>
<address>Langgt 24</address>
<city/>
<country/>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>11.2</price>
</item>
</shiporder>
<shiporder orderid="1235">
<orderperson>shong</orderperson>
<shipto>
<name>talend</name>
<address>Langgt 24</address>
<city/>
<country/>
</shipto>
<item>
<title>Hide your heart</title>
<note>note2</note>
<quantity>1</quantity>
<price>19.9</price>
</item>
</shiporder>
</root>

Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Convert a Delimited File to XML output with an XSD

Thanks Shong, that's exactly what I was looking for.
One Star

Re: Convert a Delimited File to XML output with an XSD

I was just quoted 6 weeks by a consultant to do the exact thing you have provided here.. I did it in 2 hrs (and thats just learning talend). Thank you very much and I cannot wait to tell the team about Talend
Employee

Re: Convert a Delimited File to XML output with an XSD

Hello kevinc,
Thank you for your positive feedback!
Regards,
Pcoffre.
One Star

Re: Convert a Delimited File to XML output with an XSD

I don't want to be picky but doesn't the XSD say that there should be multiple items to a shiporder?
One Star

Re: Convert a Delimited File to XML output with an XSD

how could the job design be changed so that there would be one shiporder with multiple items. janhess isn't being picky, that's the true requirement.
Community Manager

Re: Convert a Delimited File to XML output with an XSD

how could the job design be changed so that there would be one shiporder with multiple items. janhess isn't being picky, that's the true requirement.

Hi Janhess and Jebu
Set the item element as loop element and shiporder as group element.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Convert a Delimited File to XML output with an XSD

Thanks shong.
One Star

Re: Convert a Delimited File to XML output with an XSD

brilliantly easy. thanks shong.
One Star

Re: Convert a Delimited File to XML output with an XSD

Hi Shong,
Where do I find the tHashInput and output components? Can you achieve the same thing with tBufferInput ?
I have searched the Palette and the TOS Components Reference Guide, but nothing shows up.
Is this a feature of another version of the TOS software? I have tried v4.1 and v4.2.2.
thanks Bill
One Star

Re: Convert a Delimited File to XML output with an XSD

Hi,
I found the tHash* components in the ProjectSettings > Designer > PaletteSettings.
And no, the tBuffer* component didn't provide the same funtionality.
One Star

Re: Convert a Delimited File to XML output with an XSD

Hi, guys, registered to thank you for the great solution provided in here. You've saved tones of my nerves and time
One Star tw
One Star

Re: Convert a Delimited File to XML output with an XSD

Has anyone tried this with more complex XSD ? I'm new and trying with latest Talend version to produce Obama care 1094 95 xml files with IRS XSD file.