C# convert JSON object to XML dynamically

Question

I'm attempting to convert JSON sent from a web browser client to the server so I can send the data as parameters in the form of XML to an SQL database. I'm struggling to do this as I have objects inside of objects and I'm not sure how to convert them dynamically into a structured XML format. Below are examples of the JSON I am using, the possible XML formats I'm trying to turn it into (or anything close to them), and the code that I am using.

JSON:

[
    {"value":50,"name":"desired_gross_margin","type":"int"},
    {"value":50,"name":"desired_adjusted_gross_margin","type":"int"},
    {"value":0,"name":"target_electricity_tariff_unit_charge","type":"decimal"},
    {"value":0,"name":"target_electricity_tariff_standing_charge","type":"decimal"},
    {"value":0,"name":"target_gas_tariff_unit_charge","type":"decimal"},
    {"value":0,"name":"target_gas_tariff_standing_charge","type":"decimal"},
    {"value":"10/10/2016","name":"planned_go_live_date","type":"DateTime"},
    {"value":"0","name":"assumed_fuel_ratio","type":"int"},
    {"value":{
        "year_one":"Cold",
        "year_two":"Average",
        "year_three":"Warm"
        },
    "name":"weather_variable","type":"string"}
]

Possible XML outputs:

1:

<Filters>
    <CustomerParameters>
        <CustomParameter name="desired_gross_margin" type="int" value="50"/>
        <CustomParameter name="desired_adjusted_gross_margin" type="int" value="50"/>
        <CustomParameter name="target_electricity_tariff_unit_charge" type="decimal" value="0"/>
        <CustomParameter name="target_electricity_tariff_standing_charge" type="decimal" value="0"/>
        <CustomParameter name="target_gas_tariff_unit_charge" type="decimal" value="0"/>
        <CustomParameter name="target_gas_tariff_standing_charge" type="decimal" value="0"/>
        <CustomParameter name="planned_go_live_date" type="DateTime" value="10/10/2016"/>
        <CustomParameter name="assumed_fuel_ratio" type="int" value="0"/>
        <CustomParamaters name="weather_variables">
            <CustomParameter name="year_one" type="string" value="Cold"/>
            <CustomParameter name="year_two" type="string" value="Average"/>
            <CustomParameter name="year_three" type="string" value="Cold"/>
        </CustomParameters>
    </CustomParameters>
</Filters>

2:

<?xml version="1.0" encoding="UTF-8" ?>
    <0>
        <value>50</value>
        <name>desired_gross_margin</name>
        <type>int</type>
    </0>
    <1>
        <value>50</value>
        <name>desired_adjusted_gross_margin</name>
        <type>int</type>
    </1>
    <2>
        <value>0</value>
        <name>target_electricity_tariff_unit_charge</name>
        <type>decimal</type>
    </2>
    <3>
        <value>0</value>
        <name>target_electricity_tariff_standing_charge</name>
        <type>decimal</type>
    </3>
    <4>
        <value>0</value>
        <name>target_gas_tariff_unit_charge</name>
        <type>decimal</type>
    </4>
    <5>
        <value>0</value>
        <name>target_gas_tariff_standing_charge</name>
        <type>decimal</type>
    </5>
    <6>
        <value>10/10/2016</value>
        <name>planned_go_live_date</name>
        <type>DateTime</type>
    </6>
    <7>
        <value>0</value>
        <name>assumed_fuel_ratio</name>
        <type>int</type>
    </7>
    <8>
        <value>
            <year_one>Cold</year_one>
            <year_two>Average</year_two>
            <year_three>Warm</year_three>
        </value>
        <name>weather_variable</name>
        <type>string</type>
    </8>
</xml>

C# code:

ForecastController.cs:

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Web.Http;
using System.Web.Script.Serialization;
using System.Xml;

namespace ForecastServices.Controllers
{
    public class ForecastController : ApiController
    {
        [HttpPost]
        public List<Data> GetData(HttpRequestMessage request)
        {
            string connection_string = ConfigurationManager.ConnectionStrings["Database"].ConnectionString;
            string sql = "DataHub.get_GrossMarginModel";
            string json = request.Content.ReadAsStringAsync().Result;
            //var filters2 = new JavaScriptSerializer().Deserialize<dynamic>(json); //this works but I can't turn it into XML! :(
            List<Filter> filters = new JavaScriptSerializer().Deserialize<List<Filter>>(json);

            string xml = Filter.getFilterListXML(filters);

            List<Data> data = new List<Data>();

            using(SqlConnection connection = new SqlConnection(connection_string))
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.Add(new SqlParameter("filter_xml", ""));
                cmd.CommandType = CommandType.StoredProcedure;

                var adapter = new SqlDataAdapter(cmd);
                var set = new DataSet();
                cmd.ExecuteNonQuery();
                adapter.Fill(set);

                if (set.Tables.Count > 0)
                {
                    foreach (DataRow tableRow in set.Tables[0].Rows)
                    {
                        data.Add(new Data()
                        {
                            name = tableRow.ItemArray[0].ToString(),
                            year_one = (int)tableRow.ItemArray[1],
                            year_two = (int)tableRow.ItemArray[2],
                            year_three = (int)tableRow.ItemArray[3],
                        });
                    }
                }
                connection.Close();
            }
            return data;
        }
    }
}

Filter.cs:

using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;

namespace ForecastServices.Domain
{
    public class Filter
    {
        public string value { get; set; }
        public string name { get; set; }
        public string type { get; set; }

        public string getXML()
        {
            return string.Format("<CustomParameter name=\"{0}\" type=\"{1}\" value=\"{2}\"/>", name, type, value);
        }

        public static string getFilterListXML(ICollection<Filter> filters)
        {
            StringBuilder XMLString = new StringBuilder();
            XMLString.Append("<Filters><CustomerParameters>");
            foreach (Filter f in filters)
            {
                XMLString.Append(f.getXML());
            }
            XMLString.Append("</CustomParameters></Filters>");
            return XMLString.ToString();
        }
    }
}

Answer 1

Not building on your current code, you can take a look at this question

Using this one-liner:

XmlDocument doc = JsonConvert.DeserializeXmlNode("{\"Row\":" + json + "}", "root"); // JSON needs to be an object

You can end up with:

<root>
 <Row>
  <value>50</value> 
  <name>desired_gross_margin</name> 
  <type>int</type> 
  </Row>
 <Row>
  <value>50</value> 
  <name>desired_adjusted_gross_margin</name> 
  <type>int</type> 
  </Row>
 <Row>
  <value>0</value> 
  <name>target_electricity_tariff_unit_charge</name> 
  <type>decimal</type> 
  </Row>
 <Row>
  <value>0</value> 
  <name>target_electricity_tariff_standing_charge</name> 
  <type>decimal</type> 
  </Row>
 <Row>
  <value>0</value> 
  <name>target_gas_tariff_unit_charge</name> 
  <type>decimal</type> 
  </Row>
 <Row>
  <value>0</value> 
  <name>target_gas_tariff_standing_charge</name> 
  <type>decimal</type> 
  </Row>
 <Row>
  <value>10/10/2016</value> 
  <name>planned_go_live_date</name> 
  <type>DateTime</type> 
  </Row>
 <Row>
  <value>0</value> 
  <name>assumed_fuel_ratio</name> 
  <type>int</type> 
  </Row>
 <Row>
 <value>
  <year_one>Cold</year_one> 
  <year_two>Average</year_two> 
  <year_three>Warm</year_three> 
  </value>
  <name>weather_variable</name> 
  <type>string</type> 
  </Row>
  </root>

Where json is the input JSON on your question, it's close to what you want (which your post says is sort of okay), but if you want to go for your 1st and 2nd options, you can simply build new XML by manipulating this (renaming the nodes, etc.).