How to deal single element json array when converting sql xml to json convertion in c#?

Question

I am working on convert SQL output XML to JSON conversion in C#. when I convert XML that have multiple set element to JSON the output like JSON array at the sametime XML have single single set then output like JSON object. How to I maintain output as JSON array in both case?

Case 1:

<root>
   <DATA>
    <NAME>NAYAN</NAME>
    <LOCATION>CHENNAI</LOCATION>
  </DATA>
   <DATA>
    <NAME>TARA</NAME>
    <LOCATION>CHENNAI</LOCATION>
  </DATA>
 </root> 

Result:

{
   "DATA": [
      {
         "NAME": "NAYAN",
         "LOCATION": "CHENNAI"
      },
      {
         "NAME": "TARA",
         "LOCATION": "CHENNAI"
      }
   ]
}

Case 2:

<root>
   <DATA>
    <NAME>NAYAN</NAME>
    <LOCATION>CHENNAI</LOCATION>
  </DATA>
 </root>

Result:

{
   "DATA": {
      "NAME": "NAYAN",
      "LOCATION": "CHENNAI"
   }
}

Expectation:

{
   "DATA":[
 {
      "NAME": "NAYAN",
      "LOCATION": "CHENNAI"
   }
]
}

This my C# code:

static void Main(string[] args)
{
    string xml = @"<root>
      <DATA>
        <NAME>NAYAN</NAME>
        <LOCATION>CHENNAI</LOCATION>
      </DATA>
    </root>";

    XmlDocument doc = new XmlDocument();
    doc.LoadXml(xml);

    string json = JsonConvert.SerializeXmlNode(doc,Newtonsoft.Json.Formatting.Indented,true);

    Console.WriteLine(json);
}

Answer 1

Here i created a simple utility function that can take your DATA token and parse it to Array if DATA contains either object or array.

public class Utility
{
    public static string JsonParser(string json)
    {
        JToken jTokenMain = JToken.Parse(json);

        JToken jToken = jTokenMain["DATA"];

        List<object> list = new List<object>();

        if (jToken is JArray)
        {
            list = jToken.ToObject<List<object>>();
        }
        else if (jToken is JObject)
        {
            list.Add(jToken.ToObject<object>());
        }

        JToken data = JToken.FromObject(list);

        jTokenMain["DATA"] = data;

        return jTokenMain.ToString();
    }
}

You can use above function like

XmlDocument doc = new XmlDocument();
doc.LoadXml(xml);

string json = JsonConvert.SerializeXmlNode(doc, Newtonsoft.Json.Formatting.Indented, true);

string formattedJson = Utility.JsonParser(json);    

---

Edit:

In your xml <root> is the 0th level element and <DATA> is the 1st level element and its name can be anything and you want this element to be Array in json whether its object or array in xml.

XDocument doc = XDocument.Parse(xml);
//XDocument doc = XDocument.Load(@"Path to your xml");

Dictionary<string, object> dict = doc.Root.Elements()
   .GroupBy(x => x.Name.LocalName, y => new
   {
       Name = y.Element("NAME").Value,
       Location = y.Element("LOCATION").Value
   })
   .ToDictionary(x => x.Key, y => (object)y.ToList());

string json = JsonConvert.SerializeObject(dict);

Console.WriteLine(json);

Case 1: (1st level element name is DATA)

<root>
<DATA>
  <NAME>NAYAN</NAME>
  <LOCATION>CHENNAI</LOCATION>
</DATA>
</root>

Output: (Json with key name is DATA)

Case 2: (1st level element name is SAMPLE)

<root>
<SAMPLE>
  <NAME>NAYAN</NAME>
  <LOCATION>CHENNAI</LOCATION>
</SAMPLE>
</root>

Output: (Json with key name is SAMPLE)