Loading data from XML to Datatable

Help Studio
, ,
1

Hi team,
I’ve XML as my data source. The purpose of the process is loading data into the datatable. However, I’ve seen one issue. The number of the XML attributes are not consistent. In some cases, they 14, in some cases 10. In short, it is changeable. I am attaching the sample xml file for your reference.

XML Sample.txt (4.7 KB)

Here is the design of my process.

image
image741×1308 94.4 KB

Can anyone help me with this? Thanks!

2

Let us clear some constraints:

  • XML is a snippet, but we can assume, that is defining XML Namespaces
    More on this and how to handle here:

Some parts within the data are maybe a copy paste error or do have other defects (& instead of & )

After other cross-checking some alternate conversion tricks we would recommend:

  • collect all distinct child element names from the Report_Entry element - myElementNames
  • use myElementNames to construct dynamically an all over Datacolumn structure on an empty datatable
  • iterate over all Report_Entry elements
    • extract all current elements and values and use it for datatable population
3

Hi @ppr
Thank you for the help. I am not quite clear with the following suggestion. Would you elaborate a little more.

The sample xml was attached in the first post. I am attaching the screenshot of the xml that shows the variability of the number of elements.

image
image886×857 140 KB

I appreciate the guidance and help. Thanks!

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:

5

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