{"id":3290,"date":"2018-04-30T22:17:05","date_gmt":"2018-04-30T22:17:05","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=3290"},"modified":"2018-05-08T19:04:58","modified_gmt":"2018-05-08T19:04:58","slug":"convert-xml-into-json-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/convert-xml-into-json-ssis\/","title":{"rendered":"How to Convert XML into JSON using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"XML<\/a>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.
\nIn this tutorial, we will learn how to convert XML into JSON using SSIS<\/strong>. So let’s get started.<\/p>\n

<\/h2>\n

Requirements<\/h2>\n
    \n
  1. \n
      \n
    1. SSDT for business intelligence<\/a> installed.<\/li>\n
    2. ZappySys SSIS PowerPack<\/a><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

      Getting started<\/h2>\n

      A simple example to convert XML into JSON<\/h3>\n

      This example is simple, we only need to get books\u2019 information like the title, genre, price.<\/p>\n

        \n
      1. First in SSDT, drag and drop the Data Flow task:\n
      2. Press Example 1 to generate XML code. In the filter, you can add the following filter:\n
        $.store.book[*].author<\/pre>\n
        \"Filter<\/a>
        Adding filters to convert XML to JSON<\/p><\/div>\n
         <\/li>\n
      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.<\/li>\n
      4. In addition, go to your JSON file destination, go to mappings and select Add Elements below this node.<\/li>\n
      5. Select the option Add Multiple (Bound) and select the columns that you want to export to JSON:\n
      6. Run the package and you will be able to see the data exported:\n
        \"Data<\/a>
        Convert XML into JSON<\/p><\/div><\/li>\n<\/ol>\n
        How to convert array elements in XML to JSON?<\/h3>\n
        The following example shows how to convert array elements from XML into JSON using SSIS.
        \nWe will use the Example 2 of the XML Source component:<\/p>\n
        \"XML<\/a>
        XML with arrays<\/p><\/div>\n
          \n
        1. First, we will create a connection and then on mappings, we will Add Element(s) (Below this node):\n
        2. Secondly, add the id source column:\n
        3. Also, repeat the same step to add the name. The XML source configuration should be like the following:\n
        4. You can set the data time format and culture also:\n
        5. Finally, you will have the source XML like the following:\n
          \"Convert<\/a>
          JSON outoput<\/p><\/div><\/li>\n<\/ol>\n
          How to convert complex XML to JSON with a heterogeneous format<\/h3>\n
          Sometimes you have an XML with different types of arrays, elements, attribute with different formats in the same XML file.
          \nIn 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.<\/p>\n
            \n
          1. First, in the Data Flow, we will build something like this:\n
          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:\n
          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:\n
            $.root.ColDocArray.element[*]<\/pre>\n
            \"XML<\/a>
            XML filter<\/p><\/div>\n
             <\/li>\n
          4. The output displayed will be like the following in JSON format:\n
          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:\n
            $.root.ColStrArray.element[*]<\/pre>\n
            \"XML<\/a>
            XML second example<\/p><\/div>\n
             <\/li>\n
          6. Finally, the JSON destination will be the following:\n
            \"JSON<\/a>
            JSON Destination to CONVERT XML INTO JSON<\/p><\/div><\/li>\n<\/ol>\n
            Conclusion<\/h2>\n
            To conclude, we can say that the ZappySys SSIS PowerPack<\/a> 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 <\/a>FREE trial of SSIS PowerPack and try out 70+ awesome Tasks and Components.<\/strong><\/p>\n
            References<\/h2>\n