How to convert a Json Array of Json Objects into XML C#

Question

Hi I have a JS Knockout View Model posting back to my asp web app a Json Object which looks like this

[
  {
    "ID": "GRP2-SYSPRO|SysproComapnyF|0000003774|0003",
    "Requisition": "0000003774",
    "ReqnStatus": "Approved awiting Purchase Order",
    "DateReqnRaised": "2018-03-15T00:00:00",
    "ReqnValue": 573.4,
    "ApprovedValue": 0.0,
    "Originator": "*",
    "OrigName": "**********",
    "OrigEmail": "********@exidor.co.uk",
    "Line": 3.0,
    "INDX": 1,
    "DateReqnRaisedL": "15/03/18",
    "ReqStatus": "W",
    "ReqBackground": "card-heading bg-primary text-white"
  },
  {
    "ID": "GRP2-SYSPRO|SysproComapnyF|0000004304|0001",
    "Requisition": "0000004304",
    "ReqnStatus": "Approved awiting Purchase Order",
    "DateReqnRaised": "2018-08-06T00:00:00",
    "ReqnValue": 550.0,
    "ApprovedValue": 0.0,
    "Originator": "*",
    "OrigName": "********",
    "OrigEmail": "********@exidor.co.uk",
    "Line": 1.0,
    "INDX": 2,
    "DateReqnRaisedL": "06/08/18",
    "ReqStatus": "W",
    "ReqBackground": "card-heading bg-primary text-white"
  },
  {
    "ID": "GRP2-SYSPRO|SysproComapnyF|0000004434|0001",
    "Requisition": "0000004434",
    "ReqnStatus": "Awaiting Approval",
    "DateReqnRaised": "2018-09-19T00:00:00",
    "ReqnValue": 0.0,
    "ApprovedValue": 0.0,
    "Originator": "*",
    "OrigName": "********",
    "OrigEmail": "********@exidor.co.uk",
    "Line": 1.0,
    "INDX": 3,
    "DateReqnRaisedL": "19/09/18",
    "ReqStatus": "W",
    "ReqBackground": "card-heading bg-primary text-white"
  },
  {
    "ID": "GRP2-SYSPRO|SysproComapnyF|0000004550|0001",
    "Requisition": "0000004550",
    "ReqnStatus": "Approved awiting Purchase Order",
    "DateReqnRaised": "2018-10-30T00:00:00",
    "ReqnValue": 42.32,
    "ApprovedValue": 0.0,
    "Originator": "*",
    "OrigName": "********",
    "OrigEmail": "********@exidor.co.uk",
    "Line": 1.0,
    "INDX": 4,
    "DateReqnRaisedL": "30/10/18",
    "ReqStatus": "W",
    "ReqBackground": "card-heading bg-primary text-white"
  },
  {
    "ID": "GRP2-SYSPRO|SysproComapnyF|0000004645|0001",
    "Requisition": "0000004645",
    "ReqnStatus": "Awaiting Approval",
    "DateReqnRaised": "2018-11-30T00:00:00",
    "ReqnValue": 250.56,
    "ApprovedValue": 0.0,
    "Originator": "*",
    "OrigName": "********",
    "OrigEmail": "********@exidor.co.uk",
    "Line": 1.0,
    "INDX": 5,
    "DateReqnRaisedL": "30/11/18",
    "ReqStatus": "W",
    "ReqBackground": "card-heading bg-primary text-white"
  },
  {
    "ID": "GRP2-SYSPRO|SysproComapnyF|0000004659|0001",
    "Requisition": "0000004659",
    "ReqnStatus": "Approved awiting Purchase Order",
    "DateReqnRaised": "2018-12-04T00:00:00",
    "ReqnValue": 228.15,
    "ApprovedValue": 0.0,
    "Originator": "*",
    "OrigName": "********",
    "OrigEmail": "********@exidor.co.uk",
    "Line": 1.0,
    "INDX": 6,
    "DateReqnRaisedL": "04/12/18",
    "ReqStatus": "W",
    "ReqBackground": "card-heading bg-primary text-white"
  },
  {
    "ID": "GRP2-SYSPRO|SysproComapnyF|0000004661|0001",
    "Requisition": "0000004661",
    "ReqnStatus": "Approved awiting Purchase Order",
    "DateReqnRaised": "2018-12-05T00:00:00",
    "ReqnValue": 528.0,
    "ApprovedValue": 0.0,
    "Originator": "***",
    "OrigName": "********",
    "OrigEmail": "********@exidor.co.uk",
    "Line": 1.0,
    "INDX": 7,
    "DateReqnRaisedL": "05/12/18",
    "ReqStatus": "W",
    "ReqBackground": "card-heading bg-primary text-white"
  }
]

The controller is to receive this item and convert it into XML Ideally something like this

<data>
       <row>
         <id>GRP2-SYSPRO|SysproComapnyF|0000004334|0003</id>
         <requisition>0000004334</requisition>
         <reqnStatus>Awaiting Approval</reqnStatus>
         <dateReqnRaised>2018-08-15T00:00:00</dateReqnRaised>
         etc
       </row>
    <row>
         <id>GRP2-SYSPRO|SysproComapnyF|0000004340|0005</id>
         <requisition>0000004340</requisition>
         <reqnStatus>Awaiting Approval</reqnStatus>
         <dateReqnRaised>2018-08-27T00:00:00</dateReqnRaised>
         etc
        </row>
</data>

Can this be done?

The conversion in the controller is handled by

XmlDocument doc = JsonConvert.DeserializeXmlNode(json.ToString());

At first this code I used in the controller could not serialise the xml because there was no root, the error message I received was

"typeName": "Newtonsoft.Json.JsonSerializationException", "message": "XmlNodeConverter can only convert JSON that begins with an object. Path '', line 1, position 1.",

I then amend my view model code as per here so my post command became

self.postAllReqs = function(self) {
    self.error(''); // Clear error message
var data = JSON.stringify({ data: ko.toJSON(self.Reqs) }); // convert to json

    ajaxHelper(reqsUri, 'POST', data).fail(function (jqXHR, textStatus, errorThrown) {
        self.error(errorThrown);
    });
}

The ajax Helper function is

function ajaxHelper(uri, method, data) {
    self.error(''); // Clear error message
    return $.ajax({
        type: method,
        url: uri,
        dataType: 'json',
        contentType: 'application/json',
        data: data ? JSON.stringify( data ) : null //var json = JSON.stringify({ data: data });
    }).fail(function (jqXHR, textStatus, errorThrown) {
        self.error(errorThrown);
    });
}

However this now posts the payload back as

{"rows":"[{"id":"GRP2-SYSPRO|SysproComapnyF|0000003861|0001","requisition":"0000003861","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-04-06T00:00:00","reqnValue":29.3,"approvedValue":null,"originator":"***","origName":"***","origEmail":"***@exidor.co.uk","line":1,"indx":1,"dateReqnRaisedL":"06/04/18","reqStatus":"A","reqBackground":"card-heading bg-success text-white","reqStatusLabel":"Approved","approvalBtn":"css: button btn-secondary ","approvalBtnLbl":"UnApprove","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004013|0002","requisition":"0000004013","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-05-17T00:00:00","reqnValue":60,"approvedValue":null,"originator":"***","origName":"***","origEmail":"***@exidor.co.uk","line":2,"indx":2,"dateReqnRaisedL":"17/05/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004427|0001","requisition":"0000004427","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-09-17T00:00:00","reqnValue":34.73,"approvedValue":null,"originator":"***","origName":"*****","origEmail":"*****@exidor.co.uk","line":1,"indx":3,"dateReqnRaisedL":"17/09/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004449|0001","requisition":"0000004449","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-09-24T00:00:00","reqnValue":456.9,"approvedValue":null,"originator":"*****","origName":"******","origEmail":"******@exidor.co.uk","line":1,"indx":4,"dateReqnRaisedL":"24/09/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004535|0002","requisition":"0000004535","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-10-24T00:00:00","reqnValue":270.39,"approvedValue":null,"originator":"*****","origName":"********","origEmail":"********@exidor.co.uk","line":2,"indx":5,"dateReqnRaisedL":"24/10/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004626|0001","requisition":"0000004626","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-11-26T00:00:00","reqnValue":34.59,"approvedValue":null,"originator":"***","origName":"********","origEmail":"*******@exidor.co.uk","line":1,"indx":6,"dateReqnRaisedL":"26/11/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004629|0001","requisition":"0000004629","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-11-26T00:00:00","reqnValue":34.44,"approvedValue":null,"originator":"****","origName":"*******","origEmail":"******@exidor.co.uk","line":1,"indx":7,"dateReqnRaisedL":"26/11/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"}]"}

This can be converted to XML by the controller, but not to the desired format. It seems it is being flattened to this and missing the child nodes

 <?xml version="1.0"?>
<rows>[{"id":"GRP2-SYSPRO|SysproComapnyF|0000004013|0001","requisition":"0000004013","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-05-17T00:00:00","reqnValue":60,"approvedValue":null,"originator":"***","origName":"***","origEmail":"***@exidor.co.uk","line":1,"indx":1,"dateReqnRaisedL":"17/05/18","reqStatus":"D","reqBackground":"card heading bg-secondary","reqStatusLabel":"Declined - put on hold","approvalBtn":"btn btn-success ","approvalBtnLbl":"UnApprove","declineBtnLbl":"UnDecline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004362|0001","requisition":"0000004362","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-08-29T00:00:00","reqnValue":1185,"approvedValue":null,"originator":"***","origName":"***","origEmail":"***@exidor.co.uk","line":1,"indx":2,"dateReqnRaisedL":"29/08/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004427|0001","requisition":"0000004427","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-09-17T00:00:00","reqnValue":34.73,"approvedValue":null,"originator":"***","origName":"***","origEmail":"***@exidor.co.uk","line":1,"indx":3,"dateReqnRaisedL":"17/09/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004550|0001","requisition":"0000004550","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-10-30T00:00:00","reqnValue":42.32,"approvedValue":null,"originator":"***","origName":"***","origEmail":"***@exidor.co.uk","line":1,"indx":4,"dateReqnRaisedL":"30/10/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004645|0001","requisition":"0000004645","reqnStatus":"Awaiting Approval","dateReqnRaised":"2018-11-30T00:00:00","reqnValue":250.56,"approvedValue":null,"originator":"***","origName":"***","origEmail":"***@exidor.co.uk","line":1,"indx":5,"dateReqnRaisedL":"30/11/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004652|0001","requisition":"0000004652","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-12-04T00:00:00","reqnValue":146.88,"approvedValue":null,"originator":"***","origName":"***","origEmail":"***@exidor.co.uk","line":1,"indx":6,"dateReqnRaisedL":"04/12/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004653|0001","requisition":"0000004653","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-12-04T00:00:00","reqnValue":80.7,"approvedValue":null,"originator":"***","origName":"***","origEmail":"***@exidor.co.uk","line":1,"indx":7,"dateReqnRaisedL":"04/12/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004659|0001","requisition":"0000004659","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-12-04T00:00:00","reqnValue":228.15,"approvedValue":null,"originator":"***","origName":"***","origEmail":"***@exidor.co.uk","line":1,"indx":8,"dateReqnRaisedL":"04/12/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"},{"id":"GRP2-SYSPRO|SysproComapnyF|0000004661|0002","requisition":"0000004661","reqnStatus":"Approved awiting Purchase Order","dateReqnRaised":"2018-12-05T00:00:00","reqnValue":528,"approvedValue":null,"originator":"*** ","origName":"***","origEmail":"***@exidor.co.uk","line":2,"indx":9,"dateReqnRaisedL":"05/12/18","reqStatus":"W","reqBackground":"card-heading bg-primary text-white","reqStatusLabel":"Awaiting Approval","approvalBtn":"btn btn-success ","approvalBtnLbl":"Approve","declineBtnLbl":"Decline","deleteBtnLbl":"Erase"}]</rows>

Can any one suggest a way to get this to the desire format of

 <data>
           <row>
             <id>GRP2-SYSPRO|SysproComapnyF|0000004334|0003</id>
             <requisition>0000004334</requisition>
             <reqnStatus>Awaiting Approval</reqnStatus>
             <dateReqnRaised>2018-08-15T00:00:00</dateReqnRaised>
             etc
           </row>
        <row>
             <id>GRP2-SYSPRO|SysproComapnyF|0000004340|0005</id>
             <requisition>0000004340</requisition>
             <reqnStatus>Awaiting Approval</reqnStatus>
             <dateReqnRaised>2018-08-27T00:00:00</dateReqnRaised>
             etc
            </row>
    </data>

The controller code is

// POST: api/ReqsTests
    public async Task<IHttpActionResult> PostReqsTest(object json)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }
        //convert the Json object to xml
        string xml = json.ToString();
        XmlDocument doc = JsonConvert.DeserializeXmlNode(xml);

        try
        {
            //SQL store procedure
            SqlParameter param1 = new SqlParameter("@XmlIn", doc.InnerXml);
           db.Database.ExecuteSqlCommand("exec [CHC_Web].[TestWebHandShake] @XmlIn",
                                          param1);
        }
        catch (Exception e)
        {
            string message = e.Message;
            return ResponseMessage(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, message));
        }

        return ResponseMessage(Request.CreateResponse(HttpStatusCode.OK,"Inserted to database"));
    }

Thanks

Where's the JSON string? And why do you use `json.ToString()`, isn't `json` a string already? — Panagiotis Kanavos, Jan 08 '19 at 16:54
If this was me, I'd probably use a DTO to represent the object, read JSON into it, then just use attributes and XmlSerializer to serialize it just the way I wanted. That way, if I wanted json later, or another client wanted to send json, they wouldn't need to do anything weird. — Nikki9696, Jan 08 '19 at 16:56
As for the original error itself, it complains because the JSON string you posted as a screenshot, seems to be an array, not a single object. You can make it look like an object by appending converting it to an object property, eg `{"data": + originalJson + "}"` will result in an object with a `data` property containing an array — Panagiotis Kanavos, Jan 08 '19 at 16:59
Can you please [edit] your question to share a sample of the initial JSON as **text** rather than as a screenshot? See [*Discourage screenshots of code and/or errors*](https://meta.stackoverflow.com/a/307500) and [*Why not upload images of code on SO when asking a question*](https://meta.stackoverflow.com/a/285557) for why. — dbc, Jan 08 '19 at 18:29
That being said, the initial exception probably has the same cause as the one from [XmlNodeConverter can only convert JSON that begins with an object](https://stackoverflow.com/q/48786123). — dbc, Jan 08 '19 at 18:29
Also, it looks like your second JSON sample has been "double stringified", i.e. the JSON is of the form `{"data" : "some string containing stringified JSON"}`. But I'm not certain because string escaping has also been applied. Can you share the raw JSON for the second sample as text without the escaping? See [Stop visual studio debug putting slash in string containing double quotes](https://stackoverflow.com/q/41172620) for how. — dbc, Jan 08 '19 at 18:35
Finally, how were you originally posting your JSON before you did `data: data ? JSON.stringify( data ) : null`? — dbc, Jan 08 '19 at 19:04
I have amended the question for the above comments @dbc. The first post back was `var data = self.Reqs` where self.Reqs is the Knockout array of objects (I can post the model code if it helps). I think is it is these child objects which incorrectly formatted — Ian W, Jan 13 '19 at 23:04

How to convert a Json Array of Json Objects into XML C#

0 Answers0