Reduce Database Roundtrips Using
XML
Connecting to and executing commands against a database is a simple task. We do it without much thought because in today’s applications, data access is common place and necessary. For those of us who work in a Microsoft ‘Shop’, ADO and ADO.NET is the norm when it comes to reading and writing from and to a database.
Often times, I need to execute the same command with different arguments repeatedly. This is easy to do and there are many methods of doing so. Developers could use a looping construct, holding open a connection until the process has exited the loop. Developers could use a ‘bulk insert’ by executing a SQL script similar to: “insert into table1 (field1, field2, field3) select field1, field2, field3 from table2”. Most often, I see developers passing in a delimited list of values to a single stored procedure. The argument is then parsed and a cursor loops through the values executing the appropriate commands.
I would like to look at the latter option, but with a SQL XML spin to it. For those who are not familiar with FOR XML and OPEN XML, here is a brief intro. SQL 2000 supports two main methods for working with XML. FOR XML converts a recordset to XML. This output XML can be customized to either be node or attribute centric. It also allows for highly customizable XML using the EXPLICIT key word. OPEN XML converts a Unicode XML string to a recordset. Using XPath type queries, the XML can be parsed and queried against much like a SQL table.
So what is the problem I am trying to solve? Typically, when submitting a delimited list of values to a single stored procedure, you must be mindful of the values you are passing in. You must make sure the values do not contain the same character as the delimiter you are using. Also, there is a max length of 8000 varchar (4000 nvarchar) to be mindful of. Not to mention, parsing out the delimited values can be a bit messy and SQL Server is not optimized for string parsing. To help describe my solution, let me give you an example.
I have a simple contact database with several tables:
ContactValue (lookup table) - ContactValueID (PK) - ContactValue Contact - ContactID (PK) - FirstName - LastName - MiddleInitial - SSN - DateCreated (Default GetDate()) ContactLink - ContactID (PK) - ContactValueID (PK) - Value - DateCreated (Default GetDate())
The ContactValue table functions as a lookup table and contains a few of the different methods for contacting individuals (phone, fax, pager, email, etc). This table could grow to encompass many more contact methods. The Contact table contains basic information about a person that is not subject to frequent change. The ContactLink table establishes the one to many relationship between a contact and the possible contact methods and their corresponding values.
I have built a simple application to collect the contact information and submit it to the database. The details of the application are not important, but let me show you some pieces of the code that do the work.
Option Strict On
Option Explicit On
Imports System.Xml
Public Class ContactValue
Implements IDisposable
Private oXmlDoc As XmlDocument
Private oXmlRoot As XmlElement
Public ReadOnly Property ContactValueXML() As String
Get
Return oXmlRoot.InnerXml
End Get
End Property
Public Sub New(ByVal contactValueXML As String)
Try
oXmlDoc = New XmlDocument
oXmlDoc.LoadXml("<ROOT>" & contactValueXML &"</ROOT>")
oXmlRoot = oXmlDoc.DocumentElement()
Catch ex As Exception
Throw ex
End Try
End Sub
Public Sub SetXMLContactValue( ByVal contactValueID As Integer,
ByVal value As String)
Dim oXmlNode As XmlNode
Dim oCData As XmlCDataSection
Try
oXmlNode =oXmlRoot.SelectSingleNode("/ROOT/contactvalue[@id='" & contactValueID.ToString()& "']")
If Not oXmlNode Is Nothing Then
oCData = oXmlDoc.CreateCDataSection(value)
oXmlNode.AppendChild(oCData)
End If
Catch ex As Exception
Throw ex
Finally
oXmlNode = Nothing
End Try
End Sub
Public Sub Dispose() Implements System.IDisposable.Dispose
oXmlDoc = Nothing
oXmlRoot = Nothing
End Sub
End Class
The code above does three things. Its constructor takes an XML string argument which functions as a ‘template’ for the values you wish to assign. It provides a method to pass in a name/value pair and have it written to the XML (SetXMLContactValue) and a property to retrieve the XML (ContactValueXML).
To generate the XML for the constructor, I make a stored procedure call passing in a comma delimited list of IDs from the ContactValue table. See the code snippet below:
oParam(0) = New SqlParameter("@ContactValueIDs", SqlDbType.NVarChar, 4000)
oParam(0).Value = "1,2,3,4" 'phone, fax, pager, email
sSql = "usp_GetContactValuesXML"
oXML = Database.RunSpReturnScalar(cn, sSql, oParam)
The stored procedure returns a single column of data containing the desired XML. The XML is generated using the FOR XML clause. The code then returns the XML as an object. If no results are returned, oXML = Nothing. Below is the stored procedure code:
CREATE PROCEDURE dbo.usp_GetContactValuesXML
(
@ContactValueIDs nvarchar(4000)
)
AS
SET NOCOUNT ON
DECLARE @SQL nvarchar(4000)
--strip out any single quotes to help prevent SQL injection attack and return the XML
SET @SQL = N'SELECT contactvalueid ''id'', contactvalue ''name''
FROM contactvalue WITH (NOLOCK)
WHERE contactvalueid IN (' + Replace(@ContactValueIDs, CHAR(39), '') + ') FOR XML AUTO'
EXEC sp_ExecuteSQL @SQL
GO
The XML returned looks like this:
<contactvalue id="1" name="Phone"></contactvalue> <contactvalue id="2" name="Fax"></contactvalue> <contactvalue id="3" name="Pager"></contactvalue> <contactvalue id="4" name="Email"></contactvalue>
If the stored procedure returns results, we then need to pass the resultant XML to ContactValue class constructor and assign the values collected from the application to the class.
If Not oXML Is Nothing Then oContactValue = New oContactValue(oXML.ToString()) oContactValue.SetXMLContactValue (1, "5551212") 'phone oContactValue.SetXMLContactValue (2, "5553434") 'fax oContactValue.SetXMLContactValue (3, "5555656") 'pager oContactValue.SetXMLContactValue (4, "jwiner@jwiner.com") 'email End If
If we then make a call to the oContactValue.ContactValueXML property, the XML returned should appear as below:
<contactvalue id="1" name="Phone"><![CDATA[5551212]]></contactvalue> <contactvalue id="2" name="Fax"><![CDATA[5553434]]></contactvalue> <contactvalue id="3" name="Pager"><![CDATA[5555656]]></contactvalue> <contactvalue id="4"name="Email"><![CDATA[jwiner@jwiner.com]]></contactvalue>
At this stage, we are now ready to process the XML. To do so, we make a call to the stored procedure; passing in the ContactID and the XML returned from the oContactValue.ContactValueXML property. The code snippet is below:
sSql = "usp_SetContactValuesXML"
oParams(0) = New SqlParameter("@ContactID ", SqlDbType.Int, 4)
oParams(0).Value = ContactID
oParams(1) = New SqlParameter("@TemplateXML ", SqlDbType.NVarChar, 4000)
oParams(1).Value = oContactValue.ContactValueXML()
Database.RunSp(cn, sSql, oParams)
The stored procedure uses the OPEN XML clause to convert the XML into a logical table and then cursor through it, inserting the values into the ContactLink table. Another option would be to directly insert the results of the ‘SELECT…FROM OPENXML(…)’ query into the ContactLink table. There are pros and cons to each approach. The cursor approach is more resource intensive, but allows row by row manipulation of the data prior to any insert action. The set based approach performs much more efficiently, but does not yield the same flexibility.
The different options mentioned above are highlighted in version 1 and 2 of the stored procedures below:
Version 1:
CREATE PROCEDURE dbo.usp_SetContactValuesXML
(
@ContactID int,
@TemplateXML nvarchar(4000) = '<ROOT></ROOT>'
)
AS
SET NOCOUNT ON
DECLARE @iDoc int
DECLARE @Doc nvarchar(4000)
DECLARE @ContactValueID int
DECLARE @ContactName nvarchar(200)
DECLARE @ContactValue nvarchar(4000)
SET @Doc = '<ROOT>' + @TemplateXML + '</ROOT>'
EXEC sp_xml_preparedocument @iDoc OUTPUT, @Doc
DECLARE Contact_Cursor CURSOR FOR
--map to xml nodes and attributes and populate cursor
SELECT
id 'Id', name 'Name', contactvalue 'Value'
FROM
OPENXML (@iDoc, '/ROOT/contactvalue
', 2)
WITH (Id int '@id', Name nvarchar(200) '@name', ContactValue nvarchar(4000) '.')
OPEN Contact_Cursor
FETCH NEXT FROM Contact_Cursor INTO @ContactValueID, @ContactName, @ContactValue
WHILE @@FETCH_STATUS = 0
BEGIN
--if value is present
IF Len(RTrim(LTrim(@ContactValue))) > 0
BEGIN
--insert into ContactLink table
Insert into ContactLink(ContactID, ContactValueID, Value)
Values(@ContactID, @ContactValueID, @ContactValue)
END
FETCH NEXT FROM Contact_Cursor INTO @ContactValueID, @ContactName, @ContactValue
END
CLOSE Contact_Cursor
DEALLOCATE Contact_Cursor
EXEC sp_xml_removedocument @iDoc
GO
Version 2:
CREATE PROCEDURE dbo.usp_SetContactValuesXML ( @ContactID int, @TemplateXML nvarchar(4000) = '<ROOT></ROOT>' AS SET NOCOUNT ON DECLARE @iDoc int DECLARE @Doc nvarchar(4000) SET @Doc = '<ROOT>' + @TemplateXML + '</ROOT>' EXEC sp_xml_preparedocument @iDoc OUTPUT, @Doc Insert into ContactLink(ContactID, ContactValueID, Value) SELECT @ContactID, id 'Id', contactvalue 'Value' FROM OPENXML (@iDoc, '/ROOT/contactvalue ', 2) WITH (Id int '@id', Name nvarchar(200) '@name', ContactValue nvarchar(4000) '.') EXEC sp_xml_removedocument @iDoc GO
So there we have it. We’ve accomplished several things:
- To process multiple items, we’ve made only one call to the database (excluding the initial bootstrapping calls)
- Leveraged FOR XML and OPEN XML and created an object to encapsulate building of what used to be delimited values into a structured XML format
- Created a reusable object that can be used with other similar solutions
- Allowed for any and all values using the <![CDATA[]]> node within the XML
Limitations:
- Stored Procedure variable size is limited to nVarChar(4000)
- Must use SQL Server 2000, earlier additions do not offer XML support
Things to remember:
- Remember XML is case sensitive, so keep that in mind when writing your SELECT…FOR XML query
For more on XML support within SQL Server 2000:
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5c89.asp
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_1hd8.asp
Read related articles from Jon Winer: http://www.sqlservercentral.com/columnists/jwiner/allarticles.asp
