Geting XML in specific format c#

Question

I am working on MVC web API, where at one function I need to convert SQL DB result into XML file.

private string DbToXml(int fileID)
        {
            DataSet ds = new DataSet("Requirements");
            string connetionString = ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString;
            string XML;
            using (SqlConnection connection = new SqlConnection(connetionString))
            {
                string sql = "SELECT RequirementLabel as ID, VagueWord, Suggestion, Explanation, VaguePhrase, ContentText, VagueTypeText FROM [Test].[dbo].[Vague_Terms_View] where FileID=" + fileID;
                string XML_Output_Path = System.Configuration.ConfigurationManager.AppSettings.Get("XML_Output_Path");
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
                adapter.Fill(ds, "Requirement");
                var sb = new StringBuilder();
                XmlWriter xmlWrite = XmlWriter.Create(sb);
                ds.WriteXml(xmlWrite);
                XML = ds.GetXml();
            }

I am getting XML from this code like below which is correct.

<?xml version="1.0" encoding="utf-8"?>
<Requirements>
  <Requirement>
    <ID>Req97</ID>
    <VagueWord>or</VagueWord>
    <Suggestion>Keep each requirement in a single sentence.</Suggestion>
    <Explanation>Suggests that you are combining requirements. Requirements that contain conjunctions/disjunctions (AND/OR) are dangerous and can lead to downstream problems in defining scope of the requirement.</Explanation>
    <VaguePhrase>Marketing or Servicing</VaguePhrase>
    <ContentText>If a user is identified as Marketing or Servicing, then the Campaign Management (CM) hyperlink should be displayed.</ContentText>
    <VagueTypeText>Not Standard</VagueTypeText>
  </Requirement>
  <Requirement>
    <ID>Req97</ID>
    <VagueWord>should</VagueWord>
    <Suggestion>Use 'shall/must/will' for requirements,</Suggestion>
    <Explanation>Is often ambiguous, or inappropriate. Some readers will interpret these as optional or advisory, others as required.</Explanation>
    <ContentText>If a user is identified as Marketing or Servicing, then the Campaign Management (CM) hyperlink should be displayed.</ContentText>
    <VagueTypeText>Not Standard</VagueTypeText>
  </Requirement>
  <Requirement>
    <ID>Req98</ID>
    <VagueWord>Unless</VagueWord>
    <Suggestion>Specify each conditions explicitly. One condition per requirement.</Suggestion>
    <Explanation>Is an escape clause. Requirements with escape clauses are not testable. The word implies additional condition to the requirement.</Explanation>
    <ContentText>Unless Sleep, Latency, Noise, or apply conditions are present, the data transmissions will contain the code for Normal Operation.</ContentText>
    <VagueTypeText>Not Standard</VagueTypeText>
  </Requirement>
</Requirements>

But Now I need XML to be converted like, check for ID element inside Requirement node. If it is repeating in below Requirement nodes then rename it and all other elements inside Requirement node to append id=1 and number goes so on. Expected output for above XML is like below.

<?xml version="1.0" encoding="utf-8"?>
<Requirements>
  <Requirement>
    <ID "id=1">Req97</ID>
    <VagueWord "id=1">or</VagueWord>
    <Suggestion "id=1">Keep each requirement in a single sentence.</Suggestion>
    <Explanation "id=1">Suggests that you are combining requirements. Requirements that contain conjunctions/disjunctions (AND/OR) are dangerous and can lead to downstream problems in defining scope of the requirement.</Explanation>
    <VaguePhrase "id=1">Marketing or Servicing</VaguePhrase>
    <ContentText "id=1">If a user is identified as Marketing or Servicing, then the Campaign Management (CM) hyperlink should be displayed.</ContentText>
    <VagueTypeText "id=1">Not Standard</VagueTypeText>
  </Requirement>
  <Requirement>
    <ID "id=2">Req97</ID>
    <VagueWord "id=2">should</VagueWord>
    <Suggestion "id=2">Use 'shall/must/will' for requirements,</Suggestion>
    <Explanation "id=2">Is often ambiguous, or inappropriate. Some readers will interpret these as optional or advisory, others as required.</Explanation>
    <ContentText "id=2">If a user is identified as Marketing or Servicing, then the Campaign Management (CM) hyperlink should be displayed.</ContentText>
    <VagueTypeText "id=2">Not Standard</VagueTypeText>
  </Requirement>
  <Requirement>
    <ID>Req98</ID>
    <VagueWord>Unless</VagueWord>
    <Suggestion>Specify each conditions explicitly. One condition per requirement.</Suggestion>
    <Explanation>Is an escape clause. Requirements with escape clauses are not testable. The word implies additional condition to the requirement.</Explanation>
    <ContentText>Unless Sleep, Latency, Noise, or apply conditions are present, the data transmissions will contain the code for Normal Operation.</ContentText>
    <VagueTypeText>Not Standard</VagueTypeText>
  </Requirement>
</Requirements>

@C Sharper, I think will need to implement your own serialization. i have tryied in past and it's not easy and you will get a performance issue. I will try to found my code share with you. — Fabio Silva Lima, Nov 03 '16 at 08:46
Your `id` attribute value needs to be in quotes to make this valid xml. Why do the last nodes not have an `id`attribute? If don't want to complete your example, just leave it out to make it consistent. — Filburt, Nov 03 '16 at 08:47
@fabiosilvalima please share code if you have... that would be great help..!!! — C Sharper, Nov 03 '16 at 08:52
Have you considered querying your data using [FOR XML](https://msdn.microsoft.com/en-us/library/ms178107.aspx)? — Filburt, Nov 03 '16 at 08:55
Are you sure about `Req97`? Did you perhaps mean `Req97`? Because your example is not valid XML. — Manfred Radlwimmer, Nov 03 '16 at 14:18

Alexander Petrov · Answer 1 · 2016-11-03T14:11:17.757

2

var doc = XElement.Load("test.xml");

var dict = doc.Elements("Requirement")
    .Elements("ID")
    .GroupBy(id => id.Value)
    .ToDictionary(id => id.Key, id => id.Count() > 1 ? 1 : 0);

foreach (var req in doc.Elements("Requirement"))
{
    var id = req.Element("ID").Value;

    if (dict[id] > 0)
    {
        foreach (var elem in req.Elements())
        {
            elem.Add(new XAttribute("id", dict[id]));
        }
        dict[id]++;
    }
}

Now doc contains xml with added id attribute where needed.

edited Nov 03 '16 at 14:11

answered Nov 03 '16 at 14:05

Alexander Petrov

13,457
2
20
49

Unless he actually needs the wrong XML from above (`"id=1"` instead of `id="1"`) this should be the way to do it. – Manfred Radlwimmer Nov 03 '16 at 14:21

score 0 · Answer 2 · edited May 23 '17 at 12:00

0

You can either deserialize it (create objects from XML), modify your IDs as objects and then serialize the objects back to XML or you can edit the XML directly.

This is a good example how to edit an XML directly: How to modify existing XML file with XmlDocument and XmlNode in C#.

edited May 23 '17 at 12:00

Community

1
1

answered Nov 03 '16 at 09:00

renklus

776
6
17

score 0 · Answer 3 · answered Nov 04 '16 at 05:50

I have used below solution , not sure if its optimized

 XmlElement root = returnXmlFile.DocumentElement;
         XmlNodeList nodes = root.ChildNodes;
         for (int i = 0; i < nodes.Count; i++)
         {
             string OuterIDvalue = nodes[i].ChildNodes[0].InnerText.ToString();
             const int idVal = 1;
             int counter = 1;
             if (nodes[i].FirstChild.Attributes.Count == 0)
             {
                 for (int ctrInner = 0; ctrInner < nodes[i].ChildNodes.Count; ctrInner++)
                 {
                     XmlAttribute xKey = returnXmlFile.CreateAttribute("id");
                     xKey.Value = idVal.ToString();
                     nodes[i].ChildNodes[ctrInner].Attributes.Append(xKey);
                 }
             }      
                 for (int j = i + 1; j < nodes.Count; j++)
                 {
                     string innerIDvalue = nodes[j].ChildNodes[0].InnerText.ToString();
                     if (OuterIDvalue == innerIDvalue && nodes[j].FirstChild.Attributes.Count == 0)
                     {
                         counter++;
                         for (int ctr = 0; ctr < nodes[j].ChildNodes.Count; ctr++)
                         {
                             XmlAttribute xKey = returnXmlFile.CreateAttribute("id");
                             xKey.Value = counter.ToString();
                             nodes[j].ChildNodes[ctr].Attributes.Append(xKey);
                         }
                     }
                 }
         }
         string xmlnew = returnXmlFile.InnerXml;
         returnXmlFile.Save(FileName);
        return xmlnew;
    }

Geting XML in specific format c#

3 Answers3