Excel to XML Transforming Issue

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

burakburak
Posts: 1
Joined: Tue Apr 21, 2020 11:13 pm

Excel to XML Transforming Issue

Postby burakburak » Tue Apr 21, 2020 11:39 pm

Hello,

I've data input like below and i must to transform it to target xml schema.I hope someone can guide.

Code: Select all

ID Name Category Size Color
1   Productname1 Sports  55 Black
1   Productname1 Sports  56 Black
1   Productname1 Sports  57 Black
1   Productname1 Sports  58 Black
1   Productname1 Sports  59 Black


Example of obtanied XML output :

Code: Select all

<xml>
<?xml version="1.0" encoding="UTF-8"?>
<root>
  <element0>
<Id>1</Id>
<Name>Productname1 </Name>
<Category>Sports </Category>
<Size>55 </Size>
<Color>Black</Color>
</Element0>

  <element0>
<Id>1</Id>
<Name>Productname1 </Name>
<Category>Sports </Category>
<Size>56 </Size>
<Color>Black</Color>
</Element0>
----- Continue Like This----------------
</root>
</xml>


But i need output like this example:

Code: Select all

<xml>
<?xml version="1.0" encoding="UTF-8"?>
<root>
  <element0>
<Id>1</Id>
<Name>Productname1 </Name>
<Category>Sports </Category>
<variants>
<Size>55 </Size>
<Color>Black</Color>
</variants>
<variants>
<Size>56</Size>
<Color>Black</Color>
</variants>
<variants>
<Size>57 </Size>
<Color>Black</Color>
</variants>
</Element0>
----- Continue Like This----------------
</root>
</xml>


I guess this is really hard question but we can change input data type.I'm open to suggestions.

I don't want duplicated id or other columns in xml sheet.I need combine variants inside of node if id is duplicated.

bartonv
Posts: 145
Joined: Wed May 03, 2017 12:10 pm

Re: Excel to XML Transforming Issue

Postby bartonv » Wed Jun 03, 2020 12:56 pm

Hi Burakburak,
first of all, I would suggest reviewing our documentation (namely the first example shown in Figure 56.27. Mapping Editor) details on how to design the mapping in different situations. To demonstrate the practice, I have attached a simple example graph that resembles your use case. Worth noting is:
  • Since there are header items and line items present in the desired output XML, you would need to push these records via different input ports into XMLWriter.
  • To obtain the header records, I have used a simple Dedup component and deduplicated the records on the ID key.
  • In the XMLWriter mapping under Root > Element0 > Variants >Binding, notice the Key and Parent key properties. This is how the header and line information gets joined.
  • A good practice is to sort both input ports data on the "join" key.

Regards,
Attachments
example.grf
(2.77 KiB) Downloaded 42 times
---
Vladimir Barton
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com

Tanker62
Posts: 6
Joined: Sat Aug 15, 2020 10:48 am

Re: Excel to XML Transforming Issue

Postby Tanker62 » Wed Aug 19, 2020 6:47 pm

I don't know whether the person who asked this question could solve the problem, but I could, so, for the record, the solution described works. Thanks!


cron