How to transform xml data using datafactory pipeline

Question

How do we save data inside of an XML payload to blob storage?

input

<root>
  <alexIsAwesome>yes he is</alexIsAwesome>
  <bytes>sdfsdfjijOIJOISJDFQPWORPJkjsdlfkjlksdf==</bytes>
</root>

desired result

<root>
  <alexIsAwesome>yes he is</alexIsAwesome>
  <bytes>/blob/path/toSavedPayload</bytes>
</root>

1. save bytes somewhere in blob
2. replace bytes with URI of where bytes were saved

How do we use data factory to extract a node from XML and save it to blob?

Answer 1

As Azure Data Factory does not support XML natively, I would suggest you to go for SSIS package.

1. In the Data flow task, have XML source and read bytes from the xml into a variable of DT_Image datatype.
2. Create a script task, which uploads the byte array (DT_Image) got in step no.1 to azure blob storage as mentioned in the below. Code slightly modified for the requirement. Reference of SO post

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Auth;
using Microsoft.WindowsAzure.Storage.Blob;    

// Retrieve storage account from connection string.
    CloudStorageAccount storageAccount = CloudStorageAccount.Parse("StorageKey");

// Create the blob client.
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

// Retrieve reference to a previously created container.
CloudBlobContainer container = blobClient.GetContainerReference("mycontainer");

// Retrieve reference to a blob named "myblob".
CloudBlockBlob blockBlob = container.GetBlockBlobReference("myblob");

byte[] byteArrayIn = Dts.Variables["User::ImageVariable"].Value;

// Create or overwrite the "myblob" blob with contents from a local file.
using (var memoryStream = new MemoryStream(byteArrayIn);)
{
    blockBlob.UploadFromStream(memoryStream);
}

3. Now, host this SSIS Package in SSIS Runtime in Azure Data Factory and execute the SSIS package.

SSIS Runtime in Azure DataFactory