Bulk insert into SQL using XML data (.NET application Tool)

In this post, we will see:

• How do we insert bulk records into SQL Server using XML Data Type?

• How do we automate the process?

• Create an SP automatically to handle the data insertion.

• Transform a DataTable to XML.

Background

Sometimes, there is a need to insert bulk records into the database, this process is very simple when we have direct access to the database, but what happens when we have to extract the data from a Web Service, an ASP service or RESTful API?

Here is when I start to see all sorts of creative stuff, like hitting the database for each record that was extracted from the call, create a script on the fly, etc.

Scenario

• We have a web service that returns a DataSet (.NET)

• The schema of the DataTable could change any time without notice.

• We have to extract the data from several web servers.

How do we insert bulk records into SQL Server using XML Data Type?

Let’s insert some data based on the following XML:

XQuery in SQL Server is a statically typed language. So in order to reduce the static type and guarantee that there is indeed at most one value, you should use the positional predicate [1] to explicitly indicate that the path expression returns a singleton.

Tip: Uppercase and lowercase matter in XML (case sensitive). Opening and closing tags must match exactly. For example, <ROOT>, <Root>, and <root> are three different tags.

Let’s read the XML and insert some data.

Voila, we have data ready to be queried, it was very simple and straight forward right? Well, this is true if you have to create the script only once and it has no more than 10 columns, but what happens when you have multiple XML files with different schema, lots of data and each one goes to a different table? Yes, I know it will be a hassle to create the script, verify each data type and create all the tables.

Don’t worry, I have you covered!

The problem

• We have to extract some data from several servers, but we don’t have access to the database.

• IT Team found a clever a solution, so instead of allow access to the database, they created a Web Service that retrieves a DataSet with one single table. IT Team rules!! :P

• IT Team provided an image of the DataSet, this is what we should get when we call the Web Service, please see below.

The Solution

I know … I told you have I have you covered, but you still have to work a little bit to get this running. The only thing you have to do is create the target table based on the image, yes the one that you are going to use to store the data, then .NET will take care of the rest.

Let’s create a target table.

Now what?

We have to create the query to insert the data, but this time we are going to let .NET to do the work for us, so I’ve create an small program where you can pass the table name and the result is Stored Procedure based on the table schema. Please see below.

Stored Procedure:

Please observe that the xPath is hard-coded to “NewDataSet/XMLData”.

.NET Source Code.

This run a query against my database:

This get the job done:

What about the data?

Well the data is not XML, so we have to convert the DataTable to XML, I have a function that takes cares of this. Please see below.

Here is a sample of the XML after conversion:

We have the data, now what?

We have to insert the data into SQL… Yes I have another function that takes care of this too, you have to pass the DataTable and the SP name. Please see below.

Yes, we are done, now we can get data from the Web Service and do a bulk insert into SQL.

This solution work well enough for small / medium sized files, so anything under 2GB will be inserted very fast. Anything over 2GB will crash for sure, in these circumstances, SQLXML Bulk Loader should the best solution. I will try to cover this in my next posts.

Bonus

Let’s run some test with final program, we are going to insert 300K random rows into the database.

Data insertion took an average of 6 seconds, so it is not that bad.

Let’s review the data.

Conclusion

Welcome to XML world, this a valuable option when you want to bulk insert data from a Web Service, an ASP service or RESTful API.

Thanks for reading. Enjoy!