{"id":2009,"date":"2015-06-02T00:00:00","date_gmt":"2015-06-02T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sqlxml-bulk-loader-basics\/"},"modified":"2021-05-11T15:57:20","modified_gmt":"2021-05-11T15:57:20","slug":"sqlxml-bulk-loader-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/tools-sql-server\/sqlxml-bulk-loader-basics\/","title":{"rendered":"SQLXML Bulk Loader Basics"},"content":{"rendered":"
\n

At some point in their careers, most SQL Server developers will have faced the challenge of getting the data from an XML source file into SQL Server. Despite the ubiquity of the CSV format, and the rising star of JSON, a vast quantity of data is still exchanged as XML files.<\/p>\n

For bulk loading XML data, the standard tools in the Extract-Transform-Load (ETL) toolkit tend to be the venerable OPENXML, or slightly more modern OPENROWSET\/XQUERY, for the T-SQL purist, or for SSIS devotees the XML task.<\/p>\n

Any of these solutions work well enough for small-to-medium sized files and even quite complex XML structures, but they often don’t scale to larger loads. Anything over 2GB will crash OPENXML or SSIS in my experience. In some cases, even if they can load a large file, the time taken could prove a threat to your SLAs.<\/p>\n

In these circumstances, SQLXML Bulk Loader is your friend. It can import extremely large XML source data files into SQL Server at amazing speed, while preserving referential integrity, if required. Unfortunately, it’s not installed with SQL Server, by default, and it’s poorly explained in the SQL Server documentation. As a result, most developers either ignore it completely, or implement it poorly and it is unfairly perceived as “hard to use”. <\/p>\n

This article attempts to put the record straight. It will describe briefly how to find and install SQLXML Bulk Loader and then how to use it to load three “classic” XML file structures:<\/p>\n

    \n
  1. A simple XML format<\/li>\n
  2. Multiple tables stored as XML<\/li>\n
  3. A nested, moderately complex XML structure that maps to a relational table structure<\/li>\n<\/ol>\n

    To work through the examples, you’ll need to download the sample XML files from the “article rating” box at the top of this article. I’ve provided the kind of “semi-structured” source files that SQLXML Bulk Loader can digest with ease. You’ll also need to create a database in which to practice. In the examples, I’ve assumed you’ve created a directory called C:\\SQLXMLArticle<\/code> for the files, and created a database called CarSales_Staging<\/code>. All techniques described will work with all versions of the database from 2005 through 2014.<\/p>\n

    In a subsequent article, I will explain some of the more advanced features of SQLXML Bulk Loader, from handling attribute-centric XML to optimizing the process for bulk loads via validating the XML and integrating SQLXML Bulk Loader with SSIS, and other tricks and techniques to help you get the most out of this tool.<\/p>\n

    SQLXML Bulk Loader Use Cases<\/h2>\n

    SQLXML Bulk Loader is best used in the following situations:<\/p>\n