JSON parsing of a complex object

Question

Given below is the type of JSON response ,

{
  "?xml":{
    "@version":"1.0",
    "@encoding":"iso-8859-1"
  },
  "xmlreport":{
    "@title":"ABC: TEST Most Saved2",
    "@dates":"Week of May 19,2013",
    "columns":{
      "column":[
        {
          "@name":"Page",
          "@type":"dimension",
          "#text":"Page"
        },
        {
          "@name":"Events",
          "@type":"metric",
          "@hastotals":"true",
          "#text":"Events"
        }
      ]
    },
    "rows":{
      "row":[
        {
          "@rownum":"1",
          "cell":[
            {
              "@columnname":"page",
              "@csv":"\"http://www.ABC.com/profile/recipebox\"",
              "#text":"http://www.ABC.com/profile/recipebox"
            },
            {
              "@columnname":"events",
              "@percentage":"\"0.1%\"",
              "#text":"489"
            }
          ]
        },
        {
          "@rownum":"2",
          "cell":[
            {
              "@columnname":"page",
              "@csv":"\"http://www.ABC.com/recipes/peanut-butter-truffle-brownies/c5c602e4-007b-43e0-aaab-2f9aed89524c\"",
              "#text":"http://www.ABC.com/recip...c602e4-007b-43e0-aaab-2f9aed89524c"
            },
            {
              "@columnname":"events",
              "@percentage":"\"0.0%\"",
              "#text":"380"
            }
          ]
        }
      ]
    },
    "totals":{
      "pagetotals":{
        "total":{
          "@columnname":"events",
          "@value":"1820.000000",
          "#text":"1,820 (0.2%)"
        }
      },
      "reporttotals":{
        "total":{
          "@columnname":"events",
          "@value":"7838.000000",
          "#text":"7,838 (0.8%)"
        }
      },
      "timeperiodtotals":{
        "total":{
          "@columnname":"events",
          "@value":"955774.000000",
          "#text":"955,774 (100.0%)"
        }
      }
    }
  }
}

I am unable to parse the object.Could you please help me out how do I read the attributes and elements after parsing. I am using C#

{ 
     XmlDocument doc = new XmlDocument();
     doc.LoadXml(XML);

     string jsonText = JsonConvert.SerializeXmlNode(doc);
     //var result = Newtonsoft.Json.JsonConvert.DeserializeXmlNode(jsonText, "xmlreport");
     var results = JsonConvert.DeserializeObject<dynamic>(jsonText);

     JToken token = JObject.Parse(jsonText);
     var report = token["xmlreport"];
}

Answer 1

My understanding of the question is you've got some Xml and you need to send out json. Couple of points before we get to the code:

1) Don't convert xml to json directly as it causes issues

2) Parse the xml to objects at your end and then work out the format to return; decoupling what comes in and goes out will allow for one of the interfaces to change in the future without impacting the other as you can tweak the mapping

So, onto the code ...

Essentially parse the xml to objects to allow for further processing and then push out as json.

class Program
{
    private static string starting =
        "<xmlreport title=\"ABC: TEST Most Saved2\" dates=\"Week of May 19,2013\"><columns><column name=\"Page\" type=\"dimension\">Page</column><column name=\"Events\" type=\"metric\" hastotals=\"true\">Events</column></columns><rows><row rownum=\"1\"><cell columnname=\"page\" csv=\"http://www.ABC.com/profile/recipebox\">http://www.ABC.com/profile/recipebox</cell><cell columnname=\"events\" percentage=\"0.1%\">489</cell></row><row rownum=\"2\"><cell columnname=\"page\" csv=\"http://www.ABC.com/recipes/peanut-butter-truffle-brownies/c5c602e4-007b-43e0-aaab-2f9aed89524c\">http://www.ABC.com/recipes/peanut-butter-truffle-brownies/c5c602e4-007b-43e0-aaab-2f9aed89524c</cell><cell columnname=\"events\" percentage=\"0.0%\">380</cell></row></rows><totals><pagetotals><total columnname=\"events\" value=\"1820.00000\">1,820 (0.2%)</total></pagetotals><reporttotals><total columnname=\"events\" value=\"7838.000000\">7,838 (0.8%)</total></reporttotals><timeperiodtotals><total columnname=\"events\" value=\"955774.000000\">955,774 (100.0%)</total></timeperiodtotals></totals></xmlreport>";


    static void Main(string[] args)
    {
        // parse from xml to objects
        StringReader reader = new StringReader(starting);
        XmlSerializer xmlSerializer = new XmlSerializer(typeof(XmlReport));
        var xmlreport = (XmlReport)xmlSerializer.Deserialize(reader);

        // todo: do some process mapping ...

        // parse out as json
        var json = JsonConvert.SerializeObject(xmlreport);

        Console.WriteLine(json);
        Console.ReadLine();
    }
}

[Serializable]
[XmlRoot(ElementName = "xmlreport")]
public class XmlReport
{
    [XmlAttribute(AttributeName = "title")]
    public string Title { get; set; }
    [XmlAttribute(AttributeName = "dates")]
    public string Dates { get; set; }

    [XmlArray(ElementName = "columns")]
    [XmlArrayItem(typeof(Column), ElementName = "column")]
    public Collection<Column> Columns { get; set; }
}

[Serializable]
public class Column
{
    [XmlAttribute(AttributeName = "name")]
    public string Name { get; set; }
    [XmlAttribute(AttributeName = "type")]
    public string Type { get; set; }
}

I've tried to parse the json to the original xml to begin with so appologies if I've not interpreted it properly. I've not done the entire structure but I hope the example above gives you an idea of how to do the rest.

Hope this helps.

Answer 2

Use this (JSon.NET) and a recursive function writes all keys and values to output window.

        [TestMethod]
        public void ParseMePlease()
        {


            string s = @"{""?xml"":{""@version"":""1.0"",""@encoding"":""iso-8859-1""},
                            ""xmlreport"":{""@title"":""ABC: TEST Most Saved2"",""@dates"":""Week of May 19,2013"",
                            ""columns"":{""column"":[{""@name"":""Page"",""@type"":""dimension"",""#text"":""Page""},{""@name"":""Events"",""@type"":""metric"",""@hastotals"":""true"",""#text"":""Events""}]},
                            ""rows"":
                            {""row"":[{""@rownum"":""1"",""cell"":[{""@columnname"":""page"",""@csv"":""\""http://www.ABC.com/profile/recipebox\"""",""#text"":""http://www.ABC.com/profile/recipebox""},{""@columnname"":""events"",""@percentage"":""\""0.1%\"""",""#text"":""489""}]},
                            {""@rownum"":""2"",""cell"":[{""@columnname"":""page"",""@csv"":""\""http://www.ABC.com/recipes/peanut-butter-truffle-brownies/c5c602e4-007b-43e0-aaab-2f9aed89524c\"""",""#text"":""http://www.ABC.com/recip...c602e4-007b-43e0-aaab-2f9aed89524c""},{""@columnname"":""events"",""@percentage"":""\""0.0%\"""",""#text"":""380""}]}]},
                            ""totals"":{""pagetotals"":{""total"":{""@columnname"":""events"",""@value"":""1820.000000"",""#text"":""1,820 (0.2%)""}},
                            ""reporttotals"":{""total"":{""@columnname"":""events"",""@value"":""7838.000000"",""#text"":""7,838 (0.8%)""}},
                            ""timeperiodtotals"":{""total"":{""@columnname"":""events"",""@value"":""955774.000000"",""#text"":""955,774 (100.0%)""}}}}}";         
            var result=JsonConvert.DeserializeObject<object>(s);

            Debug.WriteLine("Right Click Result on quick watch for Result Views!(On Debug)"+result.ToString() );

            JObject jobject = ((Newtonsoft.Json.Linq.JObject)result);


            PrintDetail(jobject);

        }

        public void PrintDetail(JObject node)
        {
            foreach (var item in node)
            {                    
                Debug.WriteLine("Key:" + item.Key + " Value:" + item.Value);
                if (item.Value is JObject)
                {
                    PrintDetail((JObject)item.Value);
                }
            }
        }

Answer 3

One way of doing this is by getting the actual Data Structure of that JSON object that you have there.

Then create classes representing that data structure (if you can get a complete response -- having all properties, you just use this site to convert that to classes).

After that, deserialize that JSON object into your class using different libraries available. A sample could be this one.