I want to share with you a technique I have devised. I had a case where there was a stored procedure in a database that allowed you to look something up based on a parameter. The problem I had was that I needed to do this lookup anywhere between 1 – 200 times depending on the circumstances. I really didn’t like the idea of calling the stored procedure 200 times, so I looked into another way. Here is what I came up with, use XML.
To start off with, if you have never messed with XML or even XML in relation to SQL Server, then you typically don’t think of using XML when we are talking about SQL Server. But, with advances in SQL Server and its usage of XML data, you might find more ways to take advantage of it. And this method does just that.
To begin this exercise, we need to build a few things to setup a very simple scenario. Granted, the solution I’m going to illustrate is overkill for this scenario, but I’ll explain how you could use this method in other places.
Create some objects
CREATE TABLE [dbo].[tblDepartment](
[DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblEmployee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[DepartmentID] [int] NOT NULL
) ON [PRIMARY]
Insert some data
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Human Resources')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Sales')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Marketing')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Communications')
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('John', 'Smith', 2)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Nancy', 'Holder', 3)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Craig', 'Jones', 1)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Susan', 'Henderson', 1)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Robert', 'Craft', 4)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Jimmy', 'Foldgers', 2)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Max', 'Eilliot', 3)
Now that we have a couple of tables and some data, let’s talk about what we are wanting to do. Let’s say we have a stored procedure that will allow you to pass it a DepartmentID and it will return all of the employees who work in that department. But let’s say you need to get this information for all departments. In the example data above, you would have to call this stored procedure 4 times and then combine the 4 separate results into one result set. Seems inefficient to me. There has to be a better and easier way. This is where the XML part comes into play.
To start off, we need to first create a stored procedure that will give me what I want to look up. Consider the following.
CREATE PROCEDURE dbo.spGetDepartmentIDs
@RtnDeptID VARCHAR(MAX) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
WITH MyCTE (I) AS
(
SELECT DepartmentID
FROM tblDepartment
FOR XML RAW('item'), ROOT('items')
)
SELECT @RtnDeptID = MyCTE.[I] FROM MyCTE
SET NOCOUNT OFF;
END
So, what does spGetDepartmentIDs do? It returns all the department IDs as XML. So if I execute the following I will get some neat results.
DECLARE @xmlDeptID AS VARCHAR(max)
EXEC dbo.spGetDepartmentIDs @xmlDeptID OUTPUT
SELECT @xmlDeptID AS MyDeptIDs
Results (formatted for easy reading)
You are probably wondering why I’m using a CTE in this procedure. The reason is so that I can get back a defined column name. If you just issue the SELECT statement, the column name returned is nasty and you may not want to deal with it. So now you asking yourself, “So what, you can get back an XML string?” Now the cool part.
What if I pass this XML string onto a procedure that had a incoming parameter of an XML type? And then what if I could join to that XML data? It would be excellent, right? Here is how.
CREATE PROCEDURE dbo.spGetEmployeesForDeptByXML
@xmlDeptString XML
AS
BEGIN
SET NOCOUNT ON;
SELECT E.DepartmentID, E.EmployeeID, E.FirstName, E.LastName
FROM dbo.tblEmployee E
INNER JOIN @xmlDeptString.nodes('//items/item') AS x(item)
ON E.DepartmentID = x.item.value('@DepartmentID[1]', 'INT')
SET NOCOUNT OFF;
END
GO
Now, if I execute the following bit of code, I have my solution.
DECLARE @xmlDeptID AS VARCHAR(max)
EXEC dbo.spGetDepartmentIDs @xmlDeptID OUTPUT
EXEC spGetEmployeesForDeptByXML @xmlDeptID
Again, this is a very simple scenario and there is a much better way of doing this scenario. But, consider this technique if you were integrating with another system. Let’s say you needed to pass a list of some IDs to another system’s stored procedure and have it return the data back to you. Or say you need to insert data into another system. You could build an XML string that you could pass in one call. Again, you could call the other system’s stored procedure x times, or you would have a similar implementation as I have described above.
In terms of performance, I’m sure there is some gotchas with this. In my case, I was dealing with a small amount of data (200 records or less). So the XML string was small. I’m sure if you passed a string that had 1000 items, the join being done could be expensive. Therefore, use this method at your own risk and test it well. To learn more about the XML-type code used above, check out BOL or Google it. It is very powerful.
