Convert a Delimited File to XML output with an XSD

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[_AnonymousUser]

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

[Anonymous]

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

[janhess]

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

[_AnonymousUser]

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.

[Anonymous]

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

[janhess]

Thanks shong.

[_AnonymousUser]

brilliantly easy. thanks shong.