Loading XML data into SQL Server 2008

SQL Server DBA’s will often come across a scenario where they need to load data from an XML document into SQL Server 2005 or 2008. As XML is being used widely by many organizations to communicate and share data, it is becoming a common task for DBA’s to load XML data into SQL Server. This article provides a step by step guide on how to create an configure an SSIS package which can be used to import data from XML file to SQL Server 2008.

The first step is to launch BIDS (aka SQL Server Business Intelligence Development Studio). In BIDS select File | New | Projects, the will pop up the New Project screen. From here we need to select Business Intelligence Projects under the Project Types and then we need to select Integration Services Project under the Templates. Then you need to provide the SSIS Project name, location etc. Once you have entered the details left click OK to create the solution.  The project creation screen will look as shown in the image below:



BIDS will open up the SSIS Package Development Studio Environment. The Screen will have a Toolbox on the left and Solution Explorer and Properties on the right side of the screen. One of the most important spaces which we need to notice is the Connection Manager space which is at the bottom of the screen. In the center of the screen you will see the work space which has four tabs namely, Control Flow, Data Flow, Event Handler and Package Explorer.



To build the SSIS package you need to drag Data Flow Task from the toolbox and drop it on the Control Flow tab. Then you need to rename the Data Flow Task to Load XML and double click on it to open the Data Flow Task.  By doing so, you will notice that you are now in the Data Flow Tab of BIDS. 



To buid a Data Flow, drag the XML Source from the Data Flow Sources Toolbox and drop it on the Data Flow designer surface and rename the task as XML Source Data File. 



You need to double click the XML Source Data File task which will open the XML Source Editor where you need to select the XML file location as the value for Data Access Mode. Then you need to specify the actual path for the xml data file for XML Location and finally click the Generate… button to create and save the XSD file.