How to import data from an XML file to SQL Server

XML is a structured, platform- and application-independent data storage format that can be used in almost all modern programming languages, with additional parsing components available for older languages. This versatility makes it an ideal choice for storing and transferring data between different servers and applications. Its standardized format, with no proprietary or complex elements, simplifies data exchange and ensures compatibility across a wide range of environments.

In SQL Server, importing data from XML files into databases holds significant importance for multiple reasons:

  • Integrate external data seamlessly with the database
  • Facilitate data transformation, where XML data can be converted into relational data for more efficient querying and reporting
  • Ensure compatibility with external data sources, making it easier to work with data from different systems or platforms
  • Enrich the database with metadata and additional information about the data XML represents
  • Store and analyze complex, nested data structures, such as configuration files or hierarchical data
  • Ensure data interoperability and allow users to store, query, and manipulate XML data efficiently within the database due to SQL Server's native support for XML data types and XML functions

In this article, we'll explore how to import data from an XML file into a SQL Server database using dbForge Data Pump for SQL Server, a powerful SSMS add-in for data migration between systems from/to external data sources, including Excel, CSV, XML, JSON, Text, MS Access, and other data formats.

Prerequisites

To get started, you need to download and install dbForge Data Pump on the machine where the data import will take place. Also, ensure that you have an XML file with data you want to transfer to a SQL Server database.

When creating an XML file, you should follow some requirements:

  • Adhere to the basic XML syntax rules, including proper opening and closing tags and correct nesting
  • Specify the character encoding, such as UTF-8
  • Specify a unique migration URL ID for the file
  • Avoid using special characters such as '<' or '&' to prevent parsing errors
  • Define a display name for each element of the file

Step-by-step guide

Let's now take you through a step-by-step guide for importing data from an XML file into a SQL Server database using dbForge Data Pump.

1. In SSMS Object Explorer, right-click the required database and select Data Pump > Import Data to open the Data Import wizard.

2. On the Source file page of the Data Import wizard, select the XML format and click the more options menu in the File name field to load the source file you want to import. Alternatively, if you saved a template file with import options before, you can select it under User Templates. To proceed, click Next.

Select the source file

3. On the Destination page, select a server connection, a database, its schema, and a table to import data - a new one or an existing one. If you want to import data into a new target table, specify its name. Then, click Next.

Select the target table to import

4. On the Options page, you can configure the formatting options for imported data:

  • Check whether the encoding is set correctly or select another one from the dropdown list
  • Select Tag that identifies a table row from the dropdown list
  • Select the Consider tag attributes as table fields option, if required

Under Preview, you can view the source data and then click Next.

Select the options for data import

5. On the Data formats page, do the following:

  • Common Formats: Specify the formats for null strings, thousand and decimal separators, boolean variables, date and time. The Autodetect Date and Time format option is selected by default.
  • Column Settings: Configure the format settings for individual columns. These settings include Null String, Left Quote, Right Quote, and Date and Time. Note that if a format mask is not set, the application will identify date/time values automatically.

6. On the Mapping page, map the source columns to the target ones. If you are importing data into a new table, the application will automatically create and map all the columns. The results will be displayed in the bottom grid.

Map the source columns to the target ones

You can click View on the toolbar to view column properties. Finally, to clear the mapping of all columns, click Clear Mappings on the toolbar. To restore it, click Fill Mapping.

7. On the Modes page, select an import mode. If you import data into an existing table, all the options will be available. If you select a new table as a target data source, only the Append mode can be used.

  • Append - add records to the target table
  • Update - update a record in the target table with a matching record from the source table
  • Append/Update - update a record if it exists in the target table; otherwise, add a record
  • Delete - delete records in the target table that match records in the source table
  • Repopulate - delete all records in the target table and repopulate them from the source table

Optionally, you can select the Use a single transaction and Use bulk insert checkboxes, the latter of which reduces the quantity of statements and speeds up import but can affect the error handling mechanism.

8. On the Output page, select one of the following output options to manage the data import script:

  • Open the data import script in the internal editor
  • Save the data import script to a file. To use this option, specify the path and the file name. Additionally, you can select the Add timestamp to the file name and Open in the internal editor checkboxes
  • Import data directly to the database

Select output options to manage the data import script

9. On the Errors handling page, you can define the error processing behavior:

  • Prompt the user for an action
  • Ignore all errors
  • Abort at the first error

In addition, you can specify a path to write reports to a log file.

10. Click Import. When the data import process is completed, you can view the log file or click Finish to exit the wizard.

Notes and tips

If you want to save your import settings as templates for recurring scenarios, click Save Template.

Also, it is recommended to select at least one column with a primary key. Otherwise, some of the import modes on the Modes page will be unavailable. You can do this on the Mapping page of the wizard by selecting the corresponding checkbox in the Key column.

Specify a primary key

Note that when importing data into an existing table, columns with matching names will be automatically mapped, while any columns with different names will require manual mapping. If no columns with the same name are found, they will be mapped sequentially, starting with the 1st column in the source to the 1st column in the target, and so on.

Conclusion

To sum up, XML is a simplified markup language mostly used to store and transport data. The Data Import wizard available in dbForge Data Pump allows setting up data import from an XML file to SQL Server databases easily and efficiently, including the error handling behavior to avoid any failures and investigate them in case of any issues.