C# How to update xml file by XDocument when xml file generated by dataset WriteXml() function

Question

see my xml first which was generated by QCSavedData.WriteXml(@strQCViewAllPath); QCSavedData is dataset

<?xml version="1.0" standalone="yes"?>
<HNNMY_ViewAll>
<dgvViewAll_Vertical>
    <Section_x0020_>MS</Section_x0020_>
    <LineItem>Morgan Stanley</LineItem>
    <Revise_x0020_Date>10-09-2020</Revise_x0020_Date>
    <_x0032_010_x0020_FYA>126,966.0000</_x0032_010_x0020_FYA>
    <_x0032_011_x0020_FYA>128,810.0000</_x0032_011_x0020_FYA>
    <_x0032_012_x0020_FYA>140,948.0000</_x0032_012_x0020_FYA>
    <_x0032_013_x0020_FYA>150,090.0000</_x0032_013_x0020_FYA>
    <_x0031_Q_x0020_2014A>37,524.0000</_x0031_Q_x0020_2014A>
    <_x0032_Q_x0020_2014A>44,181.0000</_x0032_Q_x0020_2014A>
    <_x0033_Q_x0020_2014A>45,259.0000</_x0033_Q_x0020_2014A>
    <_x0034_Q_x0020_2014A>49,656.0000</_x0034_Q_x0020_2014A>
    <_x0032_014_x0020_FYA>176,620.0000</_x0032_014_x0020_FYA>
    <_x0031_Q_x0020_2015A>46,791.0000</_x0031_Q_x0020_2015A>
    <_x0032_Q_x0020_2015A>53,233.0000</_x0032_Q_x0020_2015A>
    <_x0033_Q_x0020_2015A>53,420.0000</_x0033_Q_x0020_2015A>
    <_x0034_Q_x0020_2015A>56,477.0000</_x0034_Q_x0020_2015A>
    <_x0032_015_x0020_FYA>209,921.0000</_x0032_015_x0020_FYA>
    <_x0031_Q_x0020_2016A>50,624.0000</_x0031_Q_x0020_2016A>
    <_x0032_Q_x0020_2016A>54,341.0000</_x0032_Q_x0020_2016A>
    <_x0033_Q_x0020_2016A>56,802.0000</_x0033_Q_x0020_2016A>
    <GroupKey>Consensus Model~Total Revenue Including VAT~RD_001~NBM~~1~MS</GroupKey>
  </dgvViewAll_Vertical>
  </HNNMY_ViewAll>

1) see this is <Section_x0020_>MS</Section_x0020_> how to query when section is MS because Section has x0020

2) see this one
<_x0032_010_x0020_FYA>126,966.0000</_x0032_010_x0020_FYA>
datatable has many columns like 2010 FYA, 2011 FYA .... 1Q 2014A but when datatable data saved in xml then it is showing like this way <_x0032_010_x0020_FYA>126,966.0000</_x0032_010_x0020_FYA> then how could i update value inside this tag ?

3) see this one <GroupKey>Consensus Model~Total Revenue Including VAT~RD_001~NBM~~1~MS</GroupKey>
i have to query like this way by

XDocument that when Section == GroupKey.Split('~')[5] AND Column Name is 2010 FYA then update the value inside of column 2010 FYA. means this column's value <_x0032_010_x0020_FYA>126,966.0000</_x0032_010_x0020_FYA>

Please help me with sample code which guide me how to update my xml. thanks

EDIT

---

<_x0032_010_x0020_FYA>1200</_x0032_010_x0020_FYA>

static string ToElementName(string name)
{
    var sb = new StringBuilder();
    for (int i = 0; i < name.Length; i++)
    {
        var c = name[i];

        if ( (i ==0 && Char.IsDigit(c)) || c == ' ')
        {
            sb.AppendFormat("_x{0:X4}_", (int)c);
        }
        else
        {
            sb.Append(c);
        }
    }
    return sb.ToString();
}

=======================================================================================

var tableName = XmlConvert.EncodeLocalName("dgvViewAll_Vertical"); // EncodeLocalName is not strictly needed here since dgvViewAll_Vertical happens to be a well-formed XML name
var sectionName = XmlConvert.EncodeLocalName("Section ");
var groupKeyName = XmlConvert.EncodeLocalName("GroupKey"); // EncodeLocalName is not strictly needed here since GroupKey happens to be a well-formed XML name
var columnName = XmlConvert.EncodeLocalName("2010 FYA");

var columnValue = "New Value";

var ns = xdocument.Root.Name.Namespace;
var columns = xdocument.Root
    .Elements(ns + tableName)
    // Add error handling here when groupKeyName is not found or not in the expected syntax
    .Where(e => e.Element(ns + sectionName)?.Value == e.Element(ns + groupKeyName).Value.Split('~').Last()) 
    .Select(e => e.Element(ns + columnName));

foreach (var column in columns)
{
    column.Value = columnValue;
}

https://stackoverflow.com/a/68317202/15940620

=======================================================================================

string QCViewPath_savepath = @"C:\Test.xml";
    DataSet ds = new DataSet();
    ds.ReadXml(QCViewPath_savepath);

    if (ds.Tables[0].AsEnumerable().Any(a => a.Field<string>("Section ") == "ML"
             && a.Field<string>("GroupKey").Split('~')[0].Trim() == "Consensus Model"
             && a.Field<string>("GroupKey").Split('~')[1].Trim() == "Net Revenue"
             && a.Field<string>("GroupKey").Split('~')[3].Trim() == "NBM"
             && a.Field<string>("GroupKey").Split('~')[5].Trim() == "1"
             && a.Field<string>("GroupKey").Split('~')[6].Trim() == "ML"
                    ))
    {

        ds.Tables[0].AsEnumerable().Where(a => a.Field<string>("Section ") == "ML"
             && a.Field<string>("GroupKey").Split('~')[0].Trim() == "Consensus Model"
             && a.Field<string>("GroupKey").Split('~')[1].Trim() == "Net Revenue"
             && a.Field<string>("GroupKey").Split('~')[3].Trim() == "NBM"
             && a.Field<string>("GroupKey").Split('~')[5].Trim() == "1"
             && a.Field<string>("GroupKey").Split('~')[6].Trim() == "ML"
             ).ToList<DataRow>()
             .ForEach(r =>
             {
                 r["2010 FYA"] = 1200;
             });

        //foreach (var row in rowsToUpdat)
        //{
        //    row.SetField("2010 FYA", "1200");
        //}
    }

Answer 1

Hi TZacks-2728,
The XML element name cannot start with a number or contain spaces. But the DataColumn name is OK.
So the DataSet XML Serializer will convert your column name into a valid XML element name.
Based on your situation, it looks like the invalid character has been replaced by a hexadecimal string.
You can refer to the XML Naming Rules.
XML elements must follow these naming rules:

Element names are case-sensitive
Element names must start with a letter or underscore
Element names cannot start with the letters xml (or XML, or Xml, etc)
Element names can contain letters, digits, hyphens, underscores, and periods
Element names cannot contain spaces
Any name can be used, no words are reserved (except xml).

Finally, you can use XDocument and XPath extension to update xml.
Code looks like below:

var xdoc = XDocument.Load(file);  
xdoc.XPathSelectElement("your XPath expression").Value = "changed value";  
xdoc.Save(file);  

You can also manually enumerate nodes to update.
Here is a thread you can refer to.
Best Regards,
Daniel Zhang

---

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.