XML is the widely-accepted platform language for transporting and storing data across disparate applications. It has higher flexibility than HTML since you can define your own tags along with specifying the order in which they are processed and displayed. The Microsoft SQL Server is one of the relational database management systems for small- to medium-scale organisations. Since many companies use XML to store their data, importing an XML file is one of the common tasks for a database administrator. You can import an XML file into the SQL Server using Business Intelligence Development Studio.
- Skill level:
Click Windows "Start," choose "Microsoft SQL Server" and select "SQL Server Business Intelligence Development Studio."
Select "File" from the main menu. Choose "New" from the available options and select "Project" to open the "New Project" screen. Select "Business Intelligence Projects" under the Project Types. Select Integration Services Project from the "Visual Studio installed templates." Enter Name and Location for your project. Click "OK" to create the project. The SQL Server Integration Services Package Development Studio screen will open.
Drag and drop the "Data Flow Task" from the "Toolbox" panel in the "Control Flow" tab. Rename the "Data Flow Task" by clicking inside the rectangle or accept the default value. Double-click to open the "Data Flow Task" to open the "Data Flow" tab of the Business Intelligence Development Studio.
Drag and drop the XML Source from the "Data Flow Sources" toolbox panel on the "Data Flow" designer interface screen. You can accept the default name or provide your own by clicking inside the box. Double-click the box to open the "XML Source Editor" screen.
Select "XML file location" from the drop-down menu for the "Data access mode." Click the "Browse..." tab to locate the folder where the source XML file is saved. Click "Generate XSD..." to make and save the XSD (XML Schema Definition) file. Click "OK" to go back to the original screen. Click the "Data Flow Destinations" folder to show the list of destinations.
Drag and drop "SQL Server Destination" to the designer interface screen. Link the XML Source Data File to the SQL Server Destination box with the help of the green arrow. Double-click the "SQL Server Destination" box to open the "SQL Destination Editor" screen. Click "New..." tab to open the "Configure OLE DB Connection Manager" screen. Click "New..." to open the "Connection Manager" screen. Select "Native OLE DB\SQL Server Native Client 10.0" from the drop-down menu for "Provider." Select the appropriate server from the drop-down menu for "Server name." Select the appropriate "Authentication" mode for "Log on to the server." Select the database from the drop-down menu for "Connect to a database." Click "Test Connection" to check the link to the database. Click "OK" to go back to the original screen.
Select a table, or view from the drop-down menu for "Use a table or view" where the data is imported from the XML file. Click "Mappings" to verify that all the attributes in the XML document are mapped correctly to the selected table columns in the SQL Server table. Click "OK" to go back to the "Data Flow" screen.
Right-click the package under the folder "SSIS Packages" in the "Solution Explorer" and select "Execute Package" to run the package. You will see both the tasks denoted by the boxes turned green, along with the number of records transferred once the execution is completed successfully.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for