How to convert excel to a nested XML?

[rang]

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>

[sai_krishna_somisetty (sai krishna)]

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

[rang]

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

[ppr (Peter Preuss)]

@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

[rang]

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

[ppr (Peter Preuss)]

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

[rang]

Thanks mate! I was hoping there’s a way like DataTable.WriteXml kind of thing. But your solution is simple and will suffice. Marking this as the solution.