topic Re: Convert a Delimited File to XML output with an XSD in Talend Studio

Convert a Delimited File to XML output with an XSD

Anonymous — Sat, 16 Nov 2024 13:32:25 GMT

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

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

Anonymous — Sat, 20 Feb 2010 03:44:58 GMT

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

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

Anonymous — Sun, 21 Feb 2010 23:43:53 GMT

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

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

_AnonymousUser — Sat, 19 Mar 2011 11:48:09 GMT

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

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

Anonymous — Mon, 21 Mar 2011 13:49:11 GMT

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

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

janhess — Mon, 21 Mar 2011 14:10:30 GMT

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

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

_AnonymousUser — Tue, 03 May 2011 21:20:46 GMT

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.

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

Anonymous — Wed, 04 May 2011 09:27:53 GMT

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

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

janhess — Wed, 04 May 2011 09:33:03 GMT

Thanks shong.

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

_AnonymousUser — Wed, 04 May 2011 11:48:27 GMT

brilliantly easy. thanks shong.

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

Anonymous — Tue, 25 Oct 2011 11:36:40 GMT

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

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

Anonymous — Thu, 27 Oct 2011 12:25:10 GMT

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

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

Anonymous — Fri, 28 Oct 2011 14:06:06 GMT

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

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

Anonymous — Wed, 14 Oct 2015 19:22:23 GMT

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.