Talend community forum / Convert a Delimited File to XML output with an XSD

andymc · 2010-02-19 13:26:23

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:

Code:

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:

Code:

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:

Code:

<?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

shong · 2010-02-20 04:44:58

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

Uploaded Images

andymc · 2010-02-22 00:43:53

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

kevinc · 2011-03-19 12:48:09

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

pcoffre · 2011-03-21 14:49:11

Hello kevinc,

Thank you for your positive feedback!

Regards,
Pcoffre.

janhess · 2011-03-21 15:10:30

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

jebu · 2011-05-03 22:20:46

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.

shong · 2011-05-04 10:27:53

jebu wrote:
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 · 2011-05-04 10:33:03

Thanks shong.

jebu · 2011-05-04 12:48:27

brilliantly easy. thanks shong.

Bill_WW · 2011-10-25 12:36:40

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

Bill_WW · 2011-10-27 13:25:10

Hi,
I found the tHash* components in the ProjectSettings > Designer > PaletteSettings.

And no, the tBuffer* component didn't provide the same funtionality.

Uploaded Images

tracy91 · 2011-10-28 15:06:06

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

Last edited by tracy91 (2012-02-13 07:28:29)

Announcement

Unanswered posts

#1 2010-02-19 13:26:23

Convert a Delimited File to XML output with an XSD

Code:

Code:

Code:

#2 2010-02-20 04:44:58

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

#3 2010-02-22 00:43:53

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

#4 2011-03-19 12:48:09

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

#5 2011-03-21 14:49:11

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

#6 2011-03-21 15:10:30

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

#7 2011-05-03 22:20:46

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

#8 2011-05-04 10:27:53

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

jebu wrote:

#9 2011-05-04 10:33:03

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

#10 2011-05-04 12:48:27

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

#11 2011-10-25 12:36:40

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

#12 2011-10-27 13:25:10

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

#13 2011-10-28 15:06:06

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

Board footer