How to Convert XML into JSON using SSIS

Introduction

These 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.

Requirements

1. 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 the Data Flow task

    

2. Press Example 1 to generate XML code. In the filter, you can add the following filter:
   $.store.book[*].author

   1	$.store.book[*].author

   

   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

    

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

   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

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

   Add element JSON destination

    

2. Secondly, add the id source column:
   

   Add XML id property

    

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

   Add columns

    

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

   Select JSON culture, date format

    

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

   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

    

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:
   

   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:
   $.root.ColDocArray.element[*]

   1	$.root.ColDocArray.element[*]

   

   XML filter

    

4. The output displayed will be like the following in JSON format:
   

   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:
   $.root.ColStrArray.element[*]

   1	$.root.ColStrArray.element[*]

   

   XML second example

    

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

   JSON Destination to CONVERT XML INTO JSON

Conclusion

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.

References

• • 1. SSIS XML Source (Read XML File or Web API)
    2. SSIS JSON File Destination (Create JSON File)
    3. Extensible Markup Language (XML)
    4. Introducing to JSON