The problem boils down to as below: enter image description here
PS: The datatable structure is defined as above in the image.
I am using C# core to solve the problem of removing the '[' and ']' from each of datatable column values and would like to convert the same by serializing as JSON string first and the deseralize to create well formed XML construct.
The issue I am facing is that I want the column values under column name: 'XMLNodePath' as XML node tag and values under column name: 'XMLElementValue' as XML node value( XML construct should be wrapped at the start and at the end with respectively i.e as shown in image
enter image description here enter image description here
I have tried to achieve the same with below code but it is unable to achieve the same XML construct:
using System.Web.Script.Serialization;
using Newtonsoft.Json;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, string>();
if (dr.Field<string>("SecLegInd") != "")
{
dr.Field<string>("SecLegInd").Remove(1, 1);
dr.Field<string>("SecLegInd").Remove(dr.Field<string>
("SecLegInd").Length - 1, 1);
}
if (dr.Field<string>("XMLNodePath") != "")
{
dr.Field<string>("XMLNodePath").Remove(0, 1);
dr.Field<string>("XMLNodePath").Remove(dr.Field<string>
("XMLNodePath").Length - 1, 1);
}
if (dr.Field<string>("XMLElementValue") != "")
{
dr.Field<string>("XMLElementValue").Remove(0, 1);
dr.Field<string>("XMLElementValue").Remove(dr.Field<string>
("XMLElementValue").Length - 1, 1);
}
row.Add(dr.Field<string>("XMLNodePath"), dr.Field<string>("XMLElementValue"));
rows.Add(row);
}
rows.Where(pair => pair.Count > 0)
.ToDictionary(pair => pair.Keys, pair => pair.Values);
string JSONstring = JsonConvert.SerializeObject(rows, new JsonSerializerSettings()
{
NullValueHandling = NullValueHandling.Ignore,
});
var temp = JArray.Parse(JSONstring);
temp.Descendants()
.OfType<JProperty>()
.Where(attr => attr.Value.ToString() == "")
.ToList()
.ForEach(attr => attr.Remove());
JSONstring = temp.ToString();
xml = JsonConvert.DeserializeXmlNode("{\"envelope\":" + JSONstring + "}", "envelope");
Please anyone let me know the thoughts on how to achieve the same.
<PropertyGroup> <LangVersion>latest</LangVersion> </PropertyGroup>to your project file if you are not targeting to .net5