Parse XML string into multiple columns and rows using SSIS

Introduction

In our previous blog we saw How to export XML from SQL Server using SSIS. Now in this blog, we will Parse XML string into multiple columns and rows using SSIS XML Parser Transform can (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. Support for expression to extract nested data and convert single node into multiple rows (e.g. extract orders from customer document using expression $.Customer.Orders[*])

In nutshell, this post will focus on how to Parse XML string into multiple columns and rows using respective XML Parser Transform.

Prerequisite

1. First, you will need to have SSIS installed
2. Secondly, make sure to have SSDT
3. Finally, do not forget to install ZappySys SSIS PowerPack

How to Extract Data from SQL Server Table and Parse xml string into multiple columns and rows.

Let´s start with an example. In this SSIS XML Parser Transform example, we will Parse database column string into multiple columns and rows.

1. First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
   

   Drag and Drop : SSIS Data Flow Task from SSIS Toolbox

2. Furthermore, drag and drop the OLE DB Source.
   

   OLE DB Source – Drag and Drop

3. Double click on OLE DB Source for configure it and click on OK.
   

   Configure OLE DB Source Editor

4. Now drag and drop XML Parser Transform and connect it with the ole db source.
5. Double click on XML Parser Transform and select that column from the drop down and enter sample data and click on Preview.
   

   SSIS XML Parser Transform – Parse XML Document

6. That’s it, now you can use that data in desire destination.
   

   SSIS XML Parser Transform – Parse XML Document, Convert XML to CSV or save to RDBMS (e.g. SQL Server, MySQL)

Conclusion

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 SSIS PowerPack from here (includes 70+ Components).

References

Finally, you can use the following URL for more information.

• Help File: XML Parser Transform