Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reduce Database RoundTrips Using XML

By Jon Winer,

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:

Read related articles from Jon Winer: http://www.sqlservercentral.com/columnists/jwiner/allarticles.asp

Total article views: 9399 | Views in the last 30 days: -1
 
Related Articles
ARTICLE

Contact us

Learn how to contact the staff at SQLServerCentral.com

FORUM

getting in error in using sp_executesql like Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

error like Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

FORUM

nvarchar max problem

nvarchar max not fulfilling requirements

FORUM

nvarchar -int

nvarchar -int

BLOG

Syncing your contacts

In the same vein as my blog post about Syncing your calendars, I wanted to post how to sync your con...

Tags
miscellaneous    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones