{"id":755,"date":"2016-08-30T22:42:05","date_gmt":"2016-08-30T22:42:05","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=755"},"modified":"2017-11-13T14:11:35","modified_gmt":"2017-11-13T14:11:35","slug":"ssis-import-xml-file-sql-server-parse-xml","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/ssis-import-xml-file-sql-server-parse-xml\/","title":{"rendered":"How to import XML into SQL Server using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>In this post you will learn how to import XML file into target such as SQL Server, CSV, Excel, Oracle. Converting or Importing XML\u00a0requires flattening XML structure (i.e. hierarchy) sometimes we refer this process as de-normalization.\u00a0\u00a0Parsing XML can be tricky depending on how many levels you have and from which level you need data. If you want to export XML rather than Import then check this article<\/a>.<\/p>\n

This article assumes you have installed SSIS PowerPack<\/span><\/a> and watched introduction videos of SSIS XML Source Connector<\/span><\/a>\u00a0and\u00a0SSIS\u00a0XML Parser Transform<\/span><\/a><\/p>\n

Import XML File using\u00a0SSIS XML Source<\/h2>\n

ZappySys SSIS XML Source <\/span><\/a>is a very powerful component. It supports not only reading from file but also reading from Direct XML String or REST API \/ SOAP Web Service.<\/a><\/p>\n

How ZappySys XML Source parse\/output data<\/h3>\n

XML Source<\/span><\/a>\u00a0created by ZappySys is very different than what Microsoft provides out the box because ZappySys component outputs one flat view of nested XML document (de-normalized) compared to many outputs what Microsoft XML Source produces which is hard to manage and join based on relation with parent node.<\/p>\n

With ZappySys XML component you don’t have to worry about joins because by default parent columns included along with hierarchy you extracted (e.g. If you extract $.Customer.Orders[*].OrderDetails[*] then you get all columns listed under <Order> and <Customer> nodes)<\/p>\n

\"SSIS

ZappySys XML Source (Read XML Files, Consume REST API, SOPA Web Service)<\/p><\/div>\n

Importing single or multiple XML Files<\/h3>\n

To import single files specify full XML file path<\/p>\n

Example:\u00a0 c:\\ssis\\File_001.xml<\/strong><\/p>\n

To import multiple files using XML source use wildcard pattern<\/p>\n

Example:\u00a0 c:\\ssis\\File_*.xml<\/strong><\/p>\n

\"SSIS

XML File Source – Specify File Path with wildcard pattern (Loop multiple files)<\/p><\/div>\n

\"SSIS

Importing data from multiple XML files<\/p><\/div>\n

 <\/p>\n

Using variable to make path dynamic<\/h3>\n

You can also use variable placeholders in your path to make it dynamic<\/p>\n

Example:\u00a0 c:\\ssis\\{{User::FileName}}<\/strong>.xml<\/p>\n

Importing data from XML Web Service or REST API<\/h2>\n

XML Source supports consuming REST API data or calling XML SOAP Web services. This article doesn’t include this feature in depth. But refer to learn more about this feature.<\/p>\n

\"SSIS

SSIS XML Source – Direct Mode – Call REST API web service or read from XML File<\/p><\/div>\n

 <\/p>\n

Parse XML string and split into columns using XML Parser Transform<\/h2>\n
\"SSIS

Parsing XML Data – XML String into Columns using SSIS XML Parser Transform<\/p><\/div>\n

 <\/p>\n

Other Settings (XML Source, XML Parser Transform)<\/h2>\n

Preview Data<\/h3>\n

All access modes support preview data like below. Change your settings such as Filter expression, Source etc and click preview.<\/p>\n

\"Read

Preview XML Data<\/p><\/div>\n

Filtering data using expression<\/h3>\n

You can filter XML data by specifying simple expressions (e.g. $.store.book[*]) to navigate various parts of XML where you can extract data (See above screenshot).<\/p>\n

Things to remember for filter expressions<\/p>\n