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.
- First, you will need to have SSIS installed
- Secondly, make sure to have SSDT
- 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.
- First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
- Furthermore, drag and drop the OLE DB Source.
- Double click on OLE DB Source for configure it and click on OK.
- Now drag and drop XML Parser Transform and connect it with the ole db source.
- Double click on XML Parser Transform and select that column from the drop down and enter sample data and click on Preview.
- That’s it, now you can use that data in desire destination.
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).
Finally, you can use the following URL for more information.
- Help File: XML Parser Transform