Data migration from Oracle and transformation from XML to JSon

Question

Can Azure Data Factory transform a string from Oracle (an xml) to a JSon Object to save in a collection in Cosmos as a Object? I've tried, but I only get a string (json object) as a simple attribute in Cosmos DB.

Answer 1

Per my experience, azure data factory can transfer data, but will not help you do some serialization steps. So, you data stored in cosmos db maybe like "info": "{\"Id\":\"1\",\"Name\":\"AA\",\"Address\":\"HQ - Main Line\"}".

To handle with this solution, I suggest you using Azure Function Cosmos DB Trigger. Please refer to my code:

using System;
using System.Collections.Generic;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Newtonsoft.Json.Linq;

namespace ProcessJson
{
    public class Class1
    {
        [FunctionName("DocumentUpdates")]
        public static void Run(
        [CosmosDBTrigger(databaseName:"db",collectionName: "item", ConnectionStringSetting = "CosmosDBConnection",LeaseCollectionName = "leases",
            CreateLeaseCollectionIfNotExists = true)]
        IReadOnlyList<Document> documents,
        TraceWriter log)
        {
            log.Verbose("Start.........");
            String endpointUrl = "https://***.documents.azure.com:443/";
            String authorizationKey = "***";
            String databaseId = "db";
            String collectionId = "import";

            DocumentClient client = new DocumentClient(new Uri(endpointUrl), authorizationKey);

            for (int i = 0; i < documents.Count; i++)
            {
                Document doc = documents[i];
                String info = doc.GetPropertyValue<String>("info");
                JObject o = JObject.Parse(info);

                doc.SetPropertyValue("info", o);

                client.ReplaceDocumentAsync(UriFactory.CreateDocumentUri(databaseId, collectionId, doc.Id), doc);

                log.Verbose("Update document Id " + doc.Id);
            }
        }
    }
}

In addition,you could refer to my previous case:Azure Cosmos DB SQL - how to unescape inner json property

Hope it helps you.