{"id":7788,"date":"2019-08-17T09:15:15","date_gmt":"2019-08-17T09:15:15","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7788"},"modified":"2019-09-14T05:24:08","modified_gmt":"2019-09-14T05:24:08","slug":"parse-xml-string-multiple-columns-rows-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/parse-xml-string-multiple-columns-rows-using-ssis\/","title":{"rendered":"Parse XML string into multiple columns and rows using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"SSIS<\/a>In our previous blog we saw How to export XML from SQL Server using SSIS<\/a>. Now in this blog, we will\u00a0Parse XML string into multiple columns and rows using\u00a0SSIS XML Parser Transform<\/a>\u00a0can (Helpful to extract data from raw XML string stored as database column or coming from other source). Ability to de-normalize nested XML data into flat structure.\u00a0Support for expression to extract nested data and convert single node into multiple rows (e.g. extract orders from customer document using expression $.Customer.Orders[*])<\/p>\n

In nutshell,\u00a0this post will focus on how to Parse XML string into multiple columns and rows using respective XML Parser Transform.<\/p>\n

 <\/p>\n

Prerequisite<\/h2>\n
    \n
  1. First, you will need to have SSIS installed<\/li>\n
  2. Secondly, make sure to have SSDT<\/li>\n
  3. Finally, do not forget to install\u00a0ZappySys\u00a0SSIS PowerPack<\/a><\/li>\n<\/ol>\n

    How to Extract Data from SQL Server Table and Parse\u00a0xml string into multiple columns and rows.<\/h2>\n

    Let\u00b4s start with an example. In this SSIS XML Parser Transform example, we will Parse database column string into multiple columns and rows.<\/p>\n

      \n
    1. First\u00a0of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.\n
    2. Furthermore, drag and drop the OLE DB Source.\n
    3. Double click on OLE DB Source for configure it and click on OK.\n
      \"Configure<\/a>

      Configure OLE DB Source Editor<\/p><\/div><\/li>\n

    4. Now drag and drop XML Parser Transform and connect it with the ole db source.<\/li>\n
    5. Double click on XML Parser Transform and select that column from the drop down and enter sample data and click on Preview.\n
    6. That’s it, now you can use that data in desire destination.\n
      \"SSIS<\/a>

      SSIS XML Parser Transform \u2013 Parse XML Document, Convert XML to CSV or save to RDBMS (e.g. SQL Server, MySQL)<\/p><\/div><\/li>\n<\/ol>\n

      <\/div>\n

      Conclusion<\/span><\/h2>\n

      After all, we saw you how to Parse string into multiple columns and rows using SSIS XML Parser Transform from SQL Server Table using OLE DB Connection. To explore many other scenarios not discussed in this article download\u00a0SSIS PowerPack from here (includes 70+ Components)<\/a>.<\/p>\n

      References<\/span><\/h2>\n

      Finally, you can use the following URL for more information.<\/p>\n