Converting mongoexport json to XML

Question

I export data from our mongoDb database into json file, and need to parse it to XML in order to be usable in our ETL.

Here is a sample of code I try to use to parse one line of my json export :

var json = @"{""_id"":{""$oid"":""592bbd86b029e62830c5020a""},""DraftNumber"":""A1B1CB8D"",""ProductRange"":""COMPREHENSIVE_HOME_INSURANCE"",""DraftStatus"":""QUOTATION_DRAFT"",""DraftLabel"":"""",""LastUpdateDate"":{""$date"":""2017-05-29T06:19:53.559Z""},""EndDate"":{""$date"":""2017-07-28T06:19:53.559Z""},""UserId"":""D900036"",""ProjectNumber"":""38764496"",""Identifier"":"""",""CurrencyCode"":""EUR"",""RenewalDate"":{""$date"":""0001-01-01T00:00:00.000Z""},""RenewalDay"":0,""RenewalMounth"":0,""CreationDate"":{""$date"":""2017-05-29T06:19:50.138Z""},""EffectiveHour"":""0"",""EffectiveMinute"":""0"",""HasAs"":[{""_t"":""AgreementHolder"",""_id"":{""$oid"":""000000000000000000000000""},""DistribCustomer"":{""isProspect"":true}}],""IsBasedOnProduct"":{""pricingType"":""DISCOUNT_RATE"",""pricingVersion"":""C""},""ActivityInAgreements"":[{""_t"":""AgreementRequest"",""_id"":{""$oid"":""000000000000000000000000""},""PremiumNature"":null}],""OriginalSubscriptionChannel"":""DIRECT"",""CurrentSubscriptionChannel"":""DIRECT"",""BusinessExpirationDate"":{""$date"":""0001-01-01T00:00:00.000Z""},""TechnicalExpirationDate"":{""$date"":""2017-08-27T06:19:53.559Z""},""IsEligibleToProposal"":true,""IneligibityReasonCodes"":[],""DematerialisationOfDocuments"":true}";

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

Console.WriteLine(doc);
Console.ReadKey();

And i face the following exception :

L'exception Newtonsoft.Json.JsonSerializationException n'a pas été gérée HResult=-2146233088 Message=JSON root object has multiple properties. The root object must have a single property in order to create a valid XML document. Consider specifying a DeserializeRootElementName. Path 'DraftNumber', line 1, position 57.
Source=Newtonsoft.Json StackTrace: à Newtonsoft.Json.Converters.XmlNodeConverter.DeserializeNode(JsonReader reader, IXmlDocument document, XmlNamespaceManager manager, IXmlNode currentNode) à Newtonsoft.Json.Converters.XmlNodeConverter.ReadJson(JsonReader reader, Type objectType, Object existingValue, JsonSerializer serializer) à Newtonsoft.Json.Serialization.JsonSerializerInternalReader.DeserializeConvertable(JsonConverter converter, JsonReader reader, Type objectType, Object existingValue) à Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent) à Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType) à Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonSerializerSettings settings) à Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonConverter[] converters) à Newtonsoft.Json.JsonConvert.DeserializeXmlNode(String value, String deserializeRootElementName, Boolean writeArrayAttribute) à Newtonsoft.Json.JsonConvert.DeserializeXmlNode(String value) à ConsoleApplication3.Program.Main(String[] args) dans c:\documents\s638723\documents\visual studio 2015\Projects\ConsoleApplication3\Program.cs:ligne 17 à System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) à System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) à Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() à System.Threading.ThreadHelper.ThreadStart_Context(Object state) à System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) à System.Threading.ThreadHelper.ThreadStart() InnerException:

More globally i'm wondering if the type exported by mongoexport is usable by the jsonconverter.

A well formed Xml has one tag at root level. Your JSON has an array at the root level. So the conversion is giving an error because the resultant xml has more than one element at the root. For code to work you would need to wrapped the Json with a root : {"Root" : { "_id": {.........}} — jdweng

FaizanHussainRabbani FaizanHussainRabbani · Accepted Answer · 2018-03-07T17:38:16

As the exception states, JSON has multiple properties at top level.

Simply define root like:

using (StreamReader r = new StreamReader("Json_1.json"))
{
    string json = r.ReadToEnd();
    var doc = (XmlDocument)JsonConvert.DeserializeXmlNode(json, "root");
}

Output:

<root>
    <_id>
        <_x0024_oid>592bbd86b029e62830c5020a</_x0024_oid>
    </_id>
    <DraftNumber>A1B1CB8D</DraftNumber>
    <ProductRange>COMPREHENSIVE_HOME_INSURANCE</ProductRange>
    <DraftStatus>QUOTATION_DRAFT</DraftStatus>
    <DraftLabel/>
    <LastUpdateDate>
        <_x0024_date>2017-05-29T06:19:53.559Z</_x0024_date>
    </LastUpdateDate>
    <EndDate>
        <_x0024_date>2017-07-28T06:19:53.559Z</_x0024_date>
    </EndDate>
    <UserId>D900036</UserId>
    <ProjectNumber>38764496</ProjectNumber>
    <Identifier/>
    <CurrencyCode>EUR</CurrencyCode>
    <RenewalDate>
        <_x0024_date>0001-01-01T00:00:00Z</_x0024_date>
    </RenewalDate>
    <RenewalDay>0</RenewalDay>
    <RenewalMounth>0</RenewalMounth>
    <CreationDate>
        <_x0024_date>2017-05-29T06:19:50.138Z</_x0024_date>
    </CreationDate>
    <EffectiveHour>0</EffectiveHour>
    <EffectiveMinute>0</EffectiveMinute>
    <HasAs>
        <_t>AgreementHolder</_t>
        <_id>
            <_x0024_oid>000000000000000000000000</_x0024_oid>
        </_id>
        <DistribCustomer>
            <isProspect>true</isProspect>
        </DistribCustomer>
    </HasAs>
    <IsBasedOnProduct>
        <pricingType>DISCOUNT_RATE</pricingType>
        <pricingVersion>C</pricingVersion>
    </IsBasedOnProduct>
    <ActivityInAgreements>
        <_t>AgreementRequest</_t>
        <_id>
            <_x0024_oid>000000000000000000000000</_x0024_oid>
        </_id>
        <PremiumNature />
    </ActivityInAgreements>
    <OriginalSubscriptionChannel>DIRECT</OriginalSubscriptionChannel>
    <CurrentSubscriptionChannel>DIRECT</CurrentSubscriptionChannel>
    <BusinessExpirationDate>
        <_x0024_date>0001-01-01T00:00:00Z</_x0024_date>
    </BusinessExpirationDate>
    <TechnicalExpirationDate>
        <_x0024_date>2017-08-27T06:19:53.559Z</_x0024_date>
    </TechnicalExpirationDate>
    <IsEligibleToProposal>true</IsEligibleToProposal>
    <DematerialisationOfDocuments>true</DematerialisationOfDocuments>
</root>

Converting mongoexport json to XML

1 Answers