JSON to XML, and one row for single object in C#

Question

I am looking for a conversion from JSON objects to XML single row for each object. Right now, I am able to convert to XML but, that is not I desired. Can somebody assist me? Also, I don't need some of the fields. Here are the JSON and preferred XML structure.

Example of JSON

{
   "paging": {
      "limit": 100,
      "total": 1394,
      "next": "Mg=="
   },
   "data": [
      {
         "mmsi": 538006090,
         "imo": 9700665,
         "last_known_position": {
            "timestamp": "2017-12-18T20:24:27+00:00",
            "geometry": {
               "type": "Point",
               "coordinates": [
                  60.87363,
                  -13.02203
               ]
            }
         }
      },
      {
         "mmsi": 527555481,
         "imo": 970000,
         "last_known_position": {
            "timestamp": "2017-12-18T20:24:27+00:00",
            "geometry": {
               "type": "Point",
               "coordinates": [
                  4.57883,
                  3.76899
               ]
            }
         }
      }
   ]
}

The XML that I desired

<vessel>
     <row mmsi="538006090" imo="9700665" lat="60.87363" lon="-13.02203"/>
     <row mmsi="527555481" imo="970000" lat="4.57883" lon="3.76899"/>
</vessel>

Thank you

Possible duplicate of [How to convert JSON to XML or XML to JSON?](https://stackoverflow.com/questions/814001/how-to-convert-json-to-xml-or-xml-to-json) — Andrei Odegov, May 16 '18 at 04:00
@AndreiOdegov thank you. But, as I stated before, I already managed to convert it. I just want it to be in that desired XML structure — encikpiee, May 16 '18 at 05:11

score 0 · Answer 1 · answered May 16 '18 at 08:14

A standard JSON-to-XML conversion library will never give you exactly the XML that you want; you will nearly always want to follow it with a transformation of that XML using XSLT.

The alternative is to hand-construct the XML that you want from the original JSON.

Both approaches are possible using XSLT 3.0 which I'm including for illustration purposes, but you could use the same design approach with other languages.

(a) If you do a standard json-to-xml conversion using XSLT 3.0, it will give you an XML structure like this:

<?xml version="1.0" encoding="UTF-8"?>
<map xmlns="http://www.w3.org/2005/xpath-functions">
   <map key="paging">
      <number key="limit">100</number>
      <number key="total">1394</number>
      <string key="next">Mg==</string>
   </map>
   <array key="data">
      <map>
         <number key="mmsi">538006090</number>
         <number key="imo">9700665</number>
         <map key="last_known_position">
            <string key="timestamp">2017-12-18T20:24:27+00:00</string>
            <map key="geometry">
               <string key="type">Point</string>
               <array key="coordinates">
                  <number>60.87363</number>
                  <number>-13.02203</number>
               </array>
            </map>
         </map>
      </map>
      <map>
         <number key="mmsi">527555481</number>
         <number key="imo">970000</number>
         <map key="last_known_position">
            <string key="timestamp">2017-12-18T20:24:27+00:00</string>
            <map key="geometry">
               <string key="type">Point</string>
               <array key="coordinates">
                  <number>4.57883</number>
                  <number>3.76899</number>
               </array>
            </map>
         </map>
      </map>
   </array>
</map>

which you can then transform to your desired output using:

<xsl:template name="postprocess" xpath-default-namespace=""http://www.w3.org/2005/xpath-functions">
  <vessel>
    <xsl:for-each select=".//map[number[@key='mmsi']]">
       <row mmsi="{*[@key='mmsi']}" 
            imo="*[@key='imo']" 
            lat="{.//*[@key='coordinates']/number[1]}" 
            lon="{.//*[@key='coordinates']/number[1]}"/>
    </xsl:for-each>
  </vessel>
</xsl:template>

(b) Alternatively, and perhaps more easily in this case, you can pick out the parts of the JSON that you want and construct the XML directly:

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  exclude-result-prefixes="#all"
  version="3.0">

  <xsl:template name="xsl:initial-template">
    <vessel>
      <xsl:for-each select="json-doc('test.json')?data?*">
        <xsl:variable name="coords" select="?last_known_position?geometry?coordinates"/>
        <row mmsi="{xs:integer(?mmsi)}" imo="{xs:integer(?imo)}" lat="{$coords?1}" lon="{$coords?2}"/>
      </xsl:for-each>
    </vessel>
  </xsl:template>  

</xsl:transform>

Cinchoo · Accepted Answer · 2018-05-17T17:39:02.243

With Cinchoo ETL - an open source library, you can do do the conversion easily with few lines of code

string json = @"{
    ""paging"": {

        ""limit"": 100,
        ""total"": 1394,
        ""next"": ""Mg==""
    },
    ""data"": [
        {
            ""mmsi"": 538006090,
            ""imo"": 9700665,
            ""last_known_position"": {
            ""timestamp"": ""2017-12-18T20:24:27+00:00"",
            ""geometry"": {
                ""type"": ""Point"",
                ""coordinates"": [
                    60.87363,
                    -13.02203
                ]
    }
}
        },
        {
            ""mmsi"": 527555481,
            ""imo"": 970000,
            ""last_known_position"": {
            ""timestamp"": ""2017-12-18T20:24:27+00:00"",
            ""geometry"": {
                ""type"": ""Point"",
                ""coordinates"": [
                    4.57883,
                    3.76899
                ]
            }
            }
        }
    ]
}
";
StringBuilder sb = new StringBuilder();
using (var p = ChoJSONReader.LoadText(json)
    .WithJSONPath("$..data")
    )
{
    using (var w = new ChoXmlWriter(sb)
        .Configure(c => c.RootName = "vessel")
        .Configure(c => c.NodeName = "row")
        )
    {
        w.Write(p.Select(r => new { _mmsi = r.mmsi, _imo = r.imo, _lat = r.last_known_position.geometry.coordinates[0], _lon = r.last_known_position.geometry.coordinates[1] }));
    }
}
Console.WriteLine(sb.ToString());

Output:

<vessel>
  <row mmsi="538006090" imo="9700665" lat="60.87363" lon="-13.02203" />
  <row mmsi="527555481" imo="970000" lat="4.57883" lon="3.76899" />
</vessel>

Checkout CodeProject article for some additional help.

UPDATE:

To handle special null value condition, you can write as below

string json = @"{
    ""paging"": {

        ""limit"": 100,
        ""total"": 1394,
        ""next"": ""Mg==""
    },
    ""data"": [
        {
            ""mmsi"": 538006090,
            ""imo"": 9700665,
            ""last_known_position"": {
            ""timestamp"": ""2017-12-18T20:24:27+00:00"",
            ""geometry"": {
                ""type"": ""Point"",
                ""coordinates"": [
                    60.87363,
                    -13.02203
                ]
    }
}
        },
        {
            ""mmsi"": 527555481,
            ""imo"": null,
            ""last_known_position"": {
            ""timestamp"": ""2017-12-18T20:24:27+00:00"",
            ""geometry"": {
                ""type"": ""Point"",
                ""coordinates"": [
                    4.57883,
                    3.76899
                ]
            }
            }
        }
    ]
}
";
StringBuilder sb = new StringBuilder();
using (var p = ChoJSONReader.LoadText(json)
    .WithJSONPath("$..data")
    )
{
    using (var w = new ChoXmlWriter(sb)
        .Configure(c => c.RootName = "vessel")
        .Configure(c => c.NodeName = "row")
        )
    {
        w.Write(p.Select(r => new { _mmsi = r.mmsi, _imo = r.imo == null ? "null" : r.imo, _lat = r.last_known_position.geometry.coordinates[0], _lon = r.last_known_position.geometry.coordinates[1] }));
    }
}
Console.WriteLine(sb.ToString());

Output:

<vessel>
  <row mmsi="538006090" imo="9700665" lat="60.87363" lon="-13.02203" />
  <row mmsi="527555481" imo="null" lat="4.57883" lon="3.76899" />
</vessel>

Disclaimer: I'm the author of this library.

This is easy. Thank you. Now i'm able to restructure this thing. However, how to write 'null' value into the output? Let's say that imo : null — encikpiee, May 17 '18 at 02:31

JSON to XML, and one row for single object in C#

2 Answers2