It suprises me when I run across developers that have never used (or even heard of) the OpenXML function in SQL Server. It’s a very handy tool to have in the bag of tricks, and we’re going to walk through an example using this method.
First, some background. OpenXML was introduced in SQL Server 2000. It allows you to use an XML document to pass data into a stored procedure. This document will be parsed by SQL Server and used just like a table in memory. OpenXML is great for doing anything in a batch format, such as syncing data between systems or doing bulk operations from disconnected systems. The key that makes this technique so valuable is that you can perform these operations with a single database call, rather than a lot of separate calls.
In our example, we’re going to use the sample database that everyone knows and loves…..AdventureWorks. If you don’t have this database available, you can get it here. For our example, I’m going to pass in an XML document with a list of departments, and I’m going to return a list of contact information for any employee currently working in one of those departments. If it’s been a while since you’ve seen the AdventureWorks database, here are the tables that we need to be concerned with. (and how they are related)
I’m going to create a simple XML document to represent the departments that we are interested in, it will look like this:
The stored procedure that uses the OpenXML method looks like this:
Let’s walk through this procedure and see how it works. The first thing to point out is that there are two system stored procedures that are necessary for this operation. The first is sp_xml_preparedocument. This procedure parses the XML string, and stores it in memory to use. It takes an output parameter of type Int that represents a handle to the document that will be used in the OpenXML function call. The second stored procedure used is sp_xml_removedocument. This procedure frees up the memory used by the XML file.
I called the OpenXML function on line 17 above. It takes the handle that was returned to use from the sp_xml_preparedocument procedure as a parameter, along with an XPath statement that describes how to parse the XML document. The “WITH” statement after the function call is also important, it describes how to parse the XML document. In our case, we only have a single attribute called Name. We can use our XML file just like we use any other table in a query. Notice on line 18 that I give it an alias, and then on line 20 I use the alias in an inner join just like I would any other table.
Normally we would call this procedure from some sort of data layer in our application, but for the sake of simplicity I’ll just call it from a query in SQL Management Studio. Since I’m using Management Studio to write my query, I can’t use an NText variable like our procedure is expecting, but a Varchar will work here.
Running this query yields the results we wanted, a list of contact information for the employees in the departments from our XML file.
The last thing I want to talk about is a performance trick that is worth mentioning. Although you won’t notice it in this simple contrived example, sometimes performance using OpenXML can suffer for complex queries. This could be because of large amounts of data, a large XML file, or complex joins / subqueries. There is a way to speed things up if you run into performance problems. You can load your XML document into a temp table, and then free the memory used by it right away. You can then use the temp table for your operations. Even though you won’t notice any difference in our example, this would be what our stored procedure looks like if we made this change: