Loading data from XML to Datatable

Help Studio
, ,
4

We got this so far. We assume that a datatable with an overall structure is aimed. For this we suggested:

Retrieve a distinct list with all available Elementnames (localName, without wd prefix) and use it later for a dynamic data column construction. We can do like this:

grafik
grafik810×106 5.16 KB

arrColNames | String Array =
 xDoc.Root.Descendants(xnsDefault + "Report_Entry").SelectMany(Function (x) x.Elements()).Select(Function (x) x.Name.LocalName).Distinct.toArray

xDoc - output of Deserialize XML, xnsDefault - XNamespace for wd

Constructing Datatatable - dtData = new DataTable()

  • For each Activity: item in arrColNames | TypeArgument: String
    • Add DataColumn Activity: ColumnName=item, Datatype: String

Then we can populate the datatable:

  • For each activity: rep in xDoc.Root.Descendants(xnsDefault + “Report_Entry”) | TypeArgument: XElement
    • Assign: tmpRow = dtData.NewRow
    • For each activity: ele in rep.Elements() | TypeArgument: XElement
      • Assign: tmpRow(ele.Name.LocalName) = ele.Value
    • Add Datarow Activity: DataTable: dtData, Datacolumn: tmpRow

As a quick, experimental Shortcut we can do an alternate within a single Assignment:

grafik
grafik1038×279 18.8 KB

dtData | DataTable =

JArray.FromObject(xDoc.Root.Descendants(xnsDefault + "Report_Entry").Select(Function (x) x.Elements().ToDictionary(Function (d) d.Name.LocalName, Function (d) d.Value))).ToObject(Of DataTable)

Feel free to break down longer statements to part statements and to decompose LINQ to essential activities.

RnD Screenshots done within immediate panel: