Need to Process XML Spreadsheet

I have an xml Spreadsheet, I need to read and process it but couldn’t able to read and process. How can i do it?

  1. use read text file activity
  2. use deserialize xml activity to process the file

I have already tried but couldn’t able to process. any alternate solution?

Hi @Monica_K69 ,

We would like to receive a Sample of your Xml Spreadsheet data if possible. So that we could understand what maybe the different ways to convert it into Excel. Is it possible to provide a Sample Input Data ?

SettleMaster125128.xml (29.7 KB)
I cannot read this file as this extension is not supported. Please do let me know if we have any other alternate solution.

Quick Prototype result:

Variables and Namespace Setup:


We are tricking the datatable conversion with the help of LINQ and JSON

arrJObjects =

(From row In xDoc.Root.Descendants(xnsDefault + "Table").First().Descendants(xnsDefault+"Row")
Where row.Elements().Count > 5
Let JPrps = row.Elements(xnsDefault + "Cell").Select(Function (x,i) New JProperty("Col" & i.toString, x.Value)).toArray
Select jo = New JObject(JPrps)).toArray

We filter for rows having more then 5 cells
then we are creating JProperties from the cell and its value
we convert it into a JObject
And return it as an array of JObjects

dtResult =
JSonConvert.DeserializeObject(Of DataTable)(JsonConvert.SerializeObject(arrJObjects))

Serializing arrJObject to JSON and desrializing it to datatable will create us the datatable

Next Things you can do: Handling the first row and assign it as column headers

Also have a look here:
Handling XML Namespaces:

LINQ Starter

Find starter help here:
ppr_XMLBox_SpreadSheetXML.xaml (8.3 KB)

Thank you, It has worked for me.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.