How to Convert XML into JSON using SSIS


XML to JSON introThese days, JSON is more popular and it is replacing XML because it is faster, easier to use, it is shorter because it does not require tags and uses brackets instead.
In this tutorial, we will learn how to convert XML into JSON using SSIS. So let’s get started.


    1. SSDT for business intelligence installed.
    2. ZappySys SSIS PowerPack

Getting started

A simple example to convert XML into JSON

This example is simple, we only need to get books’ information like the title, genre, price.

  1. First in SSDT, drag and drop the Data Flow task:
    Drag and drop data flow

    Drag and drop the Data Flow task


  2. Press Example 1 to generate XML code. In the filter, you can add the following filter:

    Filter XML data

    Adding filters to convert XML to JSON


  3. Also, in connection press new and add a connection to a JSON file which will be the path where the JSON file will be stored.
  4. In addition, go to your JSON file destination, go to mappings and select Add Elements below this node.
  5. Select the option Add Multiple (Bound) and select the columns that you want to export to JSON:
    Add multiple bounds

    Add multiple bounds


  6. Run the package and you will be able to see the data exported:
    Data converted to JSON

    Convert XML into JSON

How to convert array elements in XML to JSON?

The following example shows how to convert array elements from XML into JSON using SSIS.
We will use the Example 2 of the XML Source component:

XML with arrays

XML with arrays

  1. First, we will create a connection and then on mappings, we will Add Element(s) (Below this node):
    Add element JSON destination

    Add element JSON destination


  2. Secondly, add the id source column:
    Add id property

    Add XML id property


  3. Also, repeat the same step to add the name. The XML source configuration should be like the following:
    Elements added

    Add columns


  4. You can set the data time format and culture also:
    Select date format

    Select JSON culture, date format


  5. Finally, you will have the source XML like the following:
    Convert XML to JSON

    JSON outoput

How to convert complex XML to JSON with a heterogeneous format

Sometimes you have an XML with different types of arrays, elements, attribute with different formats in the same XML file.
In this new example, we will work with the example 3 of the XML JSON Source. We will extract 2 arrays into 2 different JSON destinations with the help of the Multicast and the XML parser.

  1. First, in the Data Flow, we will build something like this:
    XML Parser Transformation example

    XML Parser Transformation example


  2. Also, in the XML Source, we will use the example 3, with includes different arrays and XML elements. Check the option Output as a RAW document:
    Convert XML to JSON

    XML Source example


  3. In addition, in XML Parser Transform, copy the XML file from the source here to parse metadata. In filter we will add the following filter:

    XML filter parse data

    XML filter


  4. The output displayed will be like the following in JSON format:
    Data converted from XML to JSON

    JSON destination


  5. Also, in the other XML parser, we will do the same than with the first, but we will extract information of the ColStrArray elements. The filter used is the following:

    XML second example

    XML second example


  6. Finally, the JSON destination will be the following:



To conclude, we can say that the ZappySys SSIS PowerPack provide powerful tools to export data from XML to JSON. We test first a simple example and then we tested arrays and finally we parsed an XML file with multiple elements with a different format. Feel free to download FREE trial of SSIS PowerPack and try out 70+ awesome Tasks and Components.


Posted in REST API, SSIS XML Source (File / SOAP) and tagged , , , , .