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:

   1: <Departments>
   2:     <Department Name="Engineering" />
   3:     <Department Name="Purchasing" />
   4:     <Department Name="Human Resources" />
   5:     <Department Name="Finance" />
   6: </Departments>

The stored procedure that uses the OpenXML method looks like this:

   1: CREATE PROCEDURE psEmployeesByDepartment
   2:     @XML NText
   3: AS
   4: BEGIN
   5:     SET NOCOUNT ON;
   6:  
   7:     -- This will be the handle to the doc in memory.
   8:     DECLARE @iDoc Int
   9:  
  10:     -- Prepare the document and get a handle to it.
  11:     EXEC sp_xml_preparedocument @iDoc OUTPUT, @XML
  12:  
  13:     -- Get the contact info for the current employees.
  14:     SELECT cont.Title, cont.FirstName, cont.MiddleName, 
  15:     cont.LastName, cont.EmailAddress, input.[Name], cont.Phone
  16:     FROM 
  17:     OPENXML(@iDoc, '/Departments/Department')
  18:         WITH (Name Varchar(128)) AS input
  19:     INNER JOIN HumanResources.Department AS dept 
  20:         ON dept.[Name] = input.[Name]
  21:     INNER JOIN HumanResources.EmployeeDepartmentHistory AS hist
  22:         ON hist.DepartmentID = dept.DepartmentID
  23:     INNER JOIN HumanResources.Employee AS emp
  24:         ON emp.EmployeeID = hist.EmployeeID
  25:     INNER JOIN Person.Contact AS cont
  26:         ON cont.ContactID = emp.ContactID
  27:     WHERE hist.EndDate IS NULL
  28:  
  29:     -- Free up the memory used by the XML document.
  30:     EXEC sp_xml_removedocument @iDoc
  31: END
  32: GO

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.

   1: DECLARE @XML Varchar(2000)
   2:  
   3: SET @XML = 
   4:     '<Departments>
   5:         <Department Name="Engineering" />
   6:         <Department Name="Purchasing" />
   7:         <Department Name="Human Resources" />
   8:         <Department Name="Finance" />
   9:     </Departments>'
  10:  
  11: EXEC psEmployeesByDepartment @XML

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:

   1: CREATE PROCEDURE psEmployeesByDepartment
   2:     @XML NText
   3: AS
   4: BEGIN
   5:     SET NOCOUNT ON;
   6:  
   7:     -- Document handle.
   8:     DECLARE @iDoc Int
   9:  
  10:     -- Prepare the document and get a handle to it.
  11:     EXEC sp_xml_preparedocument @iDoc OUTPUT, @XML
  12:  
  13:     -- Create the temp table.
  14:     CREATE TABLE #Departments([Name] Varchar(128) 
  15:         COLLATE Latin1_General_CS_AS)
  16:  
  17:     -- Load the temp table.
  18:     INSERT INTO #Departments
  19:     SELECT input.[Name] 
  20:     FROM OPENXML(@iDoc, '/Departments/Department')
  21:         WITH ([Name] Varchar(128)) AS input
  22:  
  23:     -- Free up the memory used by the XML document.
  24:     EXEC sp_xml_removedocument @iDoc
  25:  
  26:     -- Get the contact info for the current employees.
  27:     SELECT cont.Title, cont.FirstName, cont.MiddleName, 
  28:     cont.LastName, cont.EmailAddress, tmp.[Name], cont.Phone
  29:     FROM 
  30:     #Departments AS tmp
  31:     INNER JOIN HumanResources.Department AS dept 
  32:         ON dept.[Name] = tmp.[Name]
  33:     INNER JOIN HumanResources.EmployeeDepartmentHistory AS hist
  34:         ON hist.DepartmentID = dept.DepartmentID
  35:     INNER JOIN HumanResources.Employee AS emp
  36:         ON emp.EmployeeID = hist.EmployeeID
  37:     INNER JOIN Person.Contact AS cont
  38:         ON cont.ContactID = emp.ContactID
  39:     WHERE hist.EndDate IS NULL
  40:  
  41:     -- Drop the temp table.
  42:     DROP TABLE #Departments
  43: END
  44: GO

kick it on DotNetKicks.com