How to convert excel to a nested XML?

How can I convert this excel table

to this XML format?

<PurchaseOrders>
   <PurchaseOrder>
      <PurchaseOrderNo>1111111</PurchaseOrderNo>
      <PurchaseOrderDate>20210503</PurchaseOrderDate>
      <ShipFrom>
         <ShipFromName>Company A</ShipFromName>
         <ShipFromCity>Tokyo</ShipFromName>
      </ShipFrom>
      <ShipTo>
         <ShipToName>Company D</ShipFromName>
         <ShipToCity>New York</ShipFromName>
      </ShipTo>
     <LineItems>
        <LineItem>
           <ItemLineNumber>1</ItemLineNumber>
           <ItemNo>ItemNumber11</ItemNo>
           <ItemDescription>Item Description 1</ItemDescription>
        </LineItem>
     <LineItems>
   </PurchaseOrder>
   <PurchaseOrder>
      <PurchaseOrderNo>2222222</PurchaseOrderNo>
      <PurchaseOrderDate>20210504</PurchaseOrderDate>
      <ShipFrom>
         <ShipFromName>Company B</ShipFromName>
         <ShipFromCity>Sydney</ShipFromName>
      </ShipFrom>
      <ShipTo>
         <ShipToName>Company E</ShipFromName>
         <ShipToCity>New Delhi</ShipFromName>
      </ShipTo>
     <LineItems>
        <LineItem>
           <ItemLineNumber>1</ItemLineNumber>
           <ItemNo>ItemNumber12</ItemNo>
           <ItemDescription>Item Description 2</ItemDescription>
        </LineItem>
        <LineItem>
           <ItemLineNumber>2</ItemLineNumber>
           <ItemNo>ItemNumber13</ItemNo>
           <ItemDescription>Item Description 3</ItemDescription>
        </LineItem>
     <LineItems>
     .
     .
     .
   </PurchaseOrder>
</PurchaseOrders>

Hi @rang

using the package of datatable to XML from manage packages you can get the XML file.

Even refer this link once https://marketplace.uipath.com/listings/datatable-to-xml

I don’t think it’ll be able to produce a nested XML. Please see the intended output ShipFrom, ShipTo and LineItems tags.

@rang
there are following options:

  • constructing by XML Api calls
  • with the help of a template
  • using XML Stylesheets for transformation

let us know on which approach you are more interested.

Regards
Peter

Thanks @ppr! I’m not familiar with any of those but I’d say whichever is the simplest will do.

Ok, have a start with text template approach.

  • Create a text file with the structure for 1 record (1 row from Excel). Insert placeholders, that later can be replaced by the values.
    Example:
   <PurchaseOrder>
      <PurchaseOrderNo>#PO#</PurchaseOrderNo>
      <PurchaseOrderDate>#POD#</PurchaseOrderDate>
      <ShipFrom>
         <ShipFromName>#SFROMNAME#</ShipFromName>
         <ShipFromCity>#SFROMCITY#</ShipFromCity>
      </ShipFrom>
      ....... and so on
  • read in the template file

  • read in excel - read range activity

  • for each row

    • duplicate the template text for a record
    • replace the place holders by the values
  • finaly:

    • surround the records by root tag (and optional XML Prolog)
    • save the constructed XML to a text file

Just give a start on this. We will help you further on details