Excel to XML Transforming Issue

burakburak

• April 21, 2020 00:00

• Answered

Follow

Hello,

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

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 :

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

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

• 

  Vladimir Barton

  • June 03, 2020 11:56

  0

  Comment actions Permalink

  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,

  • example.grf
• 

  tanker62

  • August 19, 2020 17:47

  0

  Comment actions Permalink

  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!

Please sign in to leave a comment.