Issue with implementing JavaScriptSerializer library to seralize datatable to JSON string and remove blank values

Question

The problem boils down to as below:

PS: The datatable structure is defined as in the image: https://i.stack.imgur.com/AEaPj.png

I am using C#.Net core to solve the problem of removing the '[' and ']' from each of datatable column values. I would also like to convert the complete datatable 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 and also XML construct should be wrapped at the start and at the end with

Shown in image: https://i.stack.imgur.com/zvyuQ.png

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.

Answer 1

It is not feasible to delete the square brackets in the datatable in this way.

dr.Field<string>("XMLNodePath") gets a string, which has no relationship with the datatable, and the changes made to it will not affect the datatable.

Try something like this:

        foreach (DataRow dr in dt.Rows)  
        {  
            string SecLegInd = "";  
            string XMLNodePath = "";  
            string XMLElementValue = "";  
            if (dr.Field<string>("SecLegInd") != "")  
            {  
                SecLegInd = dr.Field<string>("SecLegInd").Remove(1, 1);  
                SecLegInd = SecLegInd.Remove(SecLegInd.Length - 1, 1);  
            }  
            if (dr.Field<string>("XMLNodePath") != "")  
            {  
                XMLNodePath = dr.Field<string>("XMLNodePath").Remove(0, 1);  
                XMLNodePath = XMLNodePath.Remove(XMLNodePath.Length - 1, 1);  
            }  
            if (dr.Field<string>("XMLElementValue") != "")  
            {  
                XMLElementValue = dr.Field<string>("XMLElementValue").Remove(0, 1);  
                XMLElementValue = XMLElementValue.Remove(XMLElementValue.Length - 1, 1);  
            }  
            row = new Dictionary<string, string>();  
            row.Add(XMLNodePath, XMLElementValue);  
            rows.Add(row);  
        }  

Or use Regex:

            foreach (DataRow dr in dt.Rows)  
            {  
                string SecLegInd = Regex.Replace(dr.Field<string>("SecLegInd"), "[\\[\\]]", string.Empty);  
                string XMLNodePath = Regex.Replace(dr.Field<string>("XMLNodePath"), "[\\[\\]]", string.Empty);  
                string XMLElementValue = Regex.Replace(dr.Field<string>("XMLElementValue"), "[\\[\\]]", string.Empty);  
  
                dr.SetField(0, SecLegInd);  
                dr.SetField(1, XMLNodePath);  
                dr.SetField(2, XMLElementValue);  
  
                row = new Dictionary<string, string>();  
                row.Add(dr.Field<string>("XMLNodePath"), dr.Field<string>("XMLElementValue"));  
                rows.Add(row);  
            }  

Update(6/22):

Copy the xml, and then use the functions of visual studio to construct a class that conforms to its structure.

This feature requires you to install certain workloads, such as ASP.NET and web development. To prevent you from being unable to use this feature without installing these workloads, I will provide you with the produced class as an attachment.

            string strs = File.ReadAllText(@"C:\...\test-xml.xml");  
  
            XmlSerializer serializer = new XmlSerializer(typeof(envelope));  
            envelope result;  
            using (TextReader reader = new StringReader(strs))  
            {  
                 result = (envelope)serializer.Deserialize(reader);  
            }  
            string JSONstring = JsonConvert.SerializeObject(result, new JsonSerializerSettings()  
            {  
                NullValueHandling = NullValueHandling.Ignore,  
            });  
            var xml = JsonConvert.DeserializeXmlNode("{\"envelope\":" + JSONstring + "}", "envelope");  

108021-envelope.txt

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Timon,

The problem I am facing is to create formatted JSON string with all hierarchy intact from datatable like below:

private static DataTable convertXMLtoDataTable()
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
XDocument Final;
IEnumerable<XElement> allElements = Final.Descendants("envelope").ToList();
dt.Columns.Add("SecLegInd", typeof(String));
dt.Columns.Add("XMLNodePath", typeof(String));
dt.Columns.Add("XMLElementValue", typeof(String));
dt.Rows.Add("", "[envelope]", "");
foreach (XElement fila in allElements.Descendants())
{
if (fila.Name == "swapSecurityService")
dt.Rows.Add("", "[swapSecurityService]", "");
if (fila.Name == "auth")
dt.Rows.Add("", "[auth]", "");
foreach (XElement childnode in fila.Descendants())
{
if (childnode.Name == "parentSec")
{
dt.Rows.Add("[swapSecurityService/parentSec]", "", "");
dt.Rows.Add("", "[swapSecurityService/parentSec/" + childnode.Name + "]", "[" + childnode.Value + "]");
}
if (childnode.Name.ToString().Trim().Contains("rcvLegSec"))
{
dt.Rows.Add("[swapSecurityService/rcvLegSec]", "", "");
dt.Rows.Add("", "[swapSecurityService/rcvLegSec/" + childnode.Name + "]", "[" + childnode.Value + "]");
}
if (childnode.Name.ToString().Trim().Contains("payLegSec"))
{
dt.Rows.Add("[swapSecurityService/payLegSec]", "", "");
dt.Rows.Add("", "[swapSecurityService/payLegSec/" + childnode.Name + "]", "[" + childnode.Value + "]");
}
else
{
dt.Rows.Add("", "[" + childnode.Name + "]", "[" + childnode.Value + "]");
if (childnode.Name == "session")
dt.Rows.Add("", "[/auth]", "");
}
}
}
dt.Rows.Add("", "[/envelope]", "");
return dt;
}107467-test-xml.xml

@Timon Yang-MSFT : I need to format the datatable values to form JSON sting and then deserialize to form the same XML format

Answer 3

@Timon Yang-MSFT : Thanks for your help..I have solved the issue with '[' in datatable rows.

Hi Timon,

The problem I am facing is to create formatted JSON string with all hierarchy intact from datatable like below and then convert the same back to fully formatted xml as attached :107532-test-xml.xml

private static DataTable convertXMLtoDataTable()
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
XDocument Final;
IEnumerable<XElement> allElements = Final.Descendants("envelope").ToList();
dt.Columns.Add("SecLegInd", typeof(String));
dt.Columns.Add("XMLNodePath", typeof(String));
dt.Columns.Add("XMLElementValue", typeof(String));
dt.Rows.Add("", "[envelope]", "");
foreach (XElement fila in allElements.Descendants())
{
if (fila.Name == "swapSecurityService")
dt.Rows.Add("", "[swapSecurityService]", "");
if (fila.Name == "auth")
dt.Rows.Add("", "[auth]", "");
foreach (XElement childnode in fila.Descendants())
{
if (childnode.Name == "parentSec")
{
dt.Rows.Add("[swapSecurityService/parentSec]", "", "");
dt.Rows.Add("", "[swapSecurityService/parentSec/" + childnode.Name + "]", "[" + childnode.Value + "]");
}
if (childnode.Name.ToString().Trim().Contains("rcvLegSec"))
{
dt.Rows.Add("[swapSecurityService/rcvLegSec]", "", "");
dt.Rows.Add("", "[swapSecurityService/rcvLegSec/" + childnode.Name + "]", "[" + childnode.Value + "]");
}
if (childnode.Name.ToString().Trim().Contains("payLegSec"))
{
dt.Rows.Add("[swapSecurityService/payLegSec]", "", "");
dt.Rows.Add("", "[swapSecurityService/payLegSec/" + childnode.Name + "]", "[" + childnode.Value + "]");
}
else
{
dt.Rows.Add("", "[" + childnode.Name + "]", "[" + childnode.Value + "]");
if (childnode.Name == "session")
dt.Rows.Add("", "[/auth]", "");
}
}
}
dt.Rows.Add("", "[/envelope]", "");
return dt;
}