One of the things that I really hate doing as a developer is passing lots of parameters to stored procedures. The main reason why I hate doing this is because I’m lazy. I can’t be bothered creating the stored procedure with all the parameters and then having to modify the parameters when I add or remove a field in a table. I also hate creating the code to pass the parameters to the db and subsequently modifying it when db changes are made.
Over the years I've experimented with different techniques for passing parameters to make my life easier. Most of these have worked out fine and others have been a real pain in the backside. When SQL Server 2000 was released, I started using XML to pass parameters to stored procedures. To date, I am still using this method.
I've modified the way I do things for SQL Server 2005 to use the new XML Data Type (instead of nvarchar) and to handle XML created via typed datasets. I've also had to make some minor changes to my application code to handle a peculiar problem caused by the way datetime fields are formatted in .Net 2.0.
Below is a very simple example of how to use XML to pass parameters to SQL Server stored procedures. This example also illustrates some of the annoyances with using XML to insert data into SQL Server and I will highlight these when we come to them.
A sample application can be downloaded from here to illustrate what is discussed below
Example
First off we need to create a simple DB with a table and a stored procedure to retrieve data from the table.
-- *** Create database
USE master
GO
CREATE DATABASE [CanbUG]
GO
USE CanbUG
GO
-- *** Create table
CREATE TABLE [dbo].[CaseDetails]
(
[CaseID] [int] IDENTITY(1,1) NOT NULL,
[CaseName] [varchar](50),
[RegistrationDate] [datetime],
[Notes] [varchar](max),
[DateLastModified] [datetime],
CONSTRAINT [PK_CaseDetails] PRIMARY KEY NONCLUSTERED
(
[CaseID] ASC
)
) ON [PRIMARY]
GO
-- *** Create Select stored Procedure
CREATE PROCEDURE [dbo].[usp_GetCaseDetails]
AS
SELECT CaseID, CaseName, RegistrationDate, Notes, DateLastModified
FROM dbo.CaseDetails
ORDER BY RegistrationDate
GO
Next we’ll insert a record into our newly created table and execute the “usp_GetCaseDetails” stored procedure to ensure everything is working fine so far.
-- *** Insert some test data
INSERT INTO CaseDetails
VALUES('Start Here','05/09/2006','Here we go','05/09/2006')
EXEC [dbo].[usp_GetCaseDetails]
In a minute we will create an insert stored procedure to insert records into our “CaseDetails” table using XML. Firstly, we need to see how to format our XML and an easy way to do this is to execute the following SQL statement:
-- *** Retrive XML
SELECT CaseID, CaseName, RegistrationDate, Notes, DateLastModified
FROM CaseDetails
ORDER BY RegistrationDate
FOR XML AUTO, ELEMENTS
Auto mode returns query results as nested XML elements. The ELEMENTS directive constructs XML in which each column value maps to an element in the XML. I have chosen this XML format as it is the default format used by a .Net 2.0 dataset when you issue the GetXML method. Your XML should look something like this:
<CaseDetails>
<CaseID>1</CaseID>
<CaseName>Start Here</CaseName>
<RegistrationDate>2006-05-09T00:00:00</RegistrationDate>
<Notes>Here we go</Notes>
<DateLastModified>2006-05-09T00:00:00</DateLastModified>
</CaseDetails>
Now let’s create the insert stored procedure and use it to insert another record.
-- *** Create Insert stored Procedure
CREATE PROCEDURE [dbo].[usp_InsertCaseDetails] @dsXML as xml
AS
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT , @dsXML
INSERT INTO CaseDetails
SELECT CaseName, RegistrationDate, Notes, DateLastModified
FROM OPENXML(@docHandle , N'/CaseDetails',2)
WITH CaseDetails
EXEC sp_xml_removedocument @docHandle
GO
-- *** Insert some more data
DECLARE @dsXML xml
SET @dsXML = '
<CaseDetails>
<CaseName>Test Case 1</CaseName>
<RegistrationDate>2006-04-20T00:00:00</RegistrationDate>
<Notes>This is my first Test Case</Notes>
<DateLastModified>2006-04-20T00:00:00</DateLastModified>
</CaseDetails>'
EXECUTE [CanbUG].[dbo].[usp_InsertCaseDetails] @dsXML
EXEC [dbo].[usp_GetCaseDetails]
GO
The key function in the “usp_InsertCaseDetails” stored procedure is OPENXML. OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear. To use OPENXML, you need to firstly execute the function"sp_XML_preparedocument" which returns a handle that can be used to access the newly created internal representation of the XML document. This handle is then passed into the OPENXML function as the "idoc" argument.
Ok, so far we have created a DB called “CanbUG”, a table called “CaseDetails”, two stored procedures called “usp_GetCaseDetails” and “usp_InsertCaseDetails” and inserted two rows of data; one of which was inserted using XML. Now we need to create our update stored procedure. This is where we come to our first annoying factor about using XML in SQL Server.
Looking at the “usp_InsertCaseDetails” stored procedure, and doing a little bit of reading on the OPENXML function, you’ll probably come to the conclusion that the update stored procedure would look something like this:
-- *** Create Update stored Procedure
CREATE PROCEDURE [dbo].[usp_UpdateCaseDetails] @dsXML as xml
AS
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT , @dsXML
UPDATE CaseDetails
SET CaseName = DataXML.CaseName,
RegistrationDate = DataXML.RegistrationDate,
Notes = DataXML.Notes,
DateLastModified = DataXML.DateLastModified
FROM OPENXML(@docHandle , N'/CaseDetails',2)
WITH CaseDetails DataXML
WHERE CaseDetails.CaseID = DataXML.CaseID
EXEC sp_xml_removedocument @docHandle
If so, you’re pretty close. The only problem is that CaseID is an Identity column which means that for some unknown reason, this column is not made available when using “TableName” as the value for the “with” argument. If you try to create this stored procedure, you’ll receive the following error which is rather misleading and annoying as the CaseID column does exist in both the table and the XML:
Msg 207, Level 16, State 1, Procedure usp_UpdateCaseDetails,
Line 15 Invalid column name 'CaseID'.
After many hours of reading documentation and various forums posts, I finally realise that I haven’t made a mistake in my stored procedure and that this is another one of Microsoft’s famous “it works that way by design” features.
Now there are two ways around this “bug”, one is to use a “SchemaDeclaration” instead of a “TableName” as the value for the “with” argument:
WITH (CaseID INT, CaseName VARCHAR(50), RegistrationDate datetime, Notes varchar(max), DateLastModified datetime) DataXML
and the other is to modify the where clause to look like this:
WHERE CaseDetails.CaseID = @dsXML.value('(/CaseDetails/CaseID)[1]', 'INT' )
I know which one I’d prefer to use, especially if my table contained 20 or 30 columns. Besides, the first option reminds me of passing parameters to stored procedures so I'm just replacing one annoyance with another and gaining nothing.
Now let’s create the update stored procedure and test if it works by updating some data.
CREATE PROCEDURE [dbo].[usp_UpdateCaseDetails] @dsXML as xml
AS
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT , @dsXML
UPDATE CaseDetails
SET CaseName = DataXML.CaseName,
RegistrationDate = DataXML.RegistrationDate,
Notes = DataXML.Notes,
DateLastModified = DataXML.DateLastModified
FROM OPENXML(@docHandle , N'/CaseDetails',2)
WITH CaseDetails DataXML
WHERE CaseDetails.CaseID = @dsXML.value('(/CaseDetails/CaseID)[1]', 'INT' )
EXEC sp_xml_removedocument @docHandle
GO
-- *** Update record
DECLARE @dsXML xml
SET @dsXML =
'<CaseDetails>
<CaseID>2</CaseID>
<CaseName>Test Case 1</CaseName>
<RegistrationDate>2006-04-20T00:00:00</RegistrationDate>
<Notes>Our new data</Notes>
<DateLastModified>2006-04-20T00:00:00</DateLastModified>
</CaseDetails>'
EXECUTE [CanbUG].[dbo].[usp_UpdateCaseDetails] @dsXML
EXEC [dbo].[usp_GetCaseDetails]
You’ll see from the returned records that the data in our table has been updated successfully.
When you use a typed dataset to store your data on the client side and then issue the GetXML method of the dataset to retrieve the XML, the format of the XML is a little different than that shown above. If the typed dataset is called dsCaseDetails, the XML turns out looking like this:
<dsCaseDetails xmlns="http://tempuri.org/dsCaseDetails.xsd">
<CaseDetails>
<CaseID>1</CaseID>
<CaseName>Start Here</CaseName>
<RegistrationDate>2006-05-09T00:00:00+11:00 </RegistrationDate>
<Notes>Here we go</Notes>
<DateLastModified>2006-05-09T00:00:00+11:00 </DateLastModified>
</CaseDetails>
</dsCaseDetails>
As you can see, the DatasetName and Namespace of the typed dataset have been appended to the XML. You may have also noticed that datetime fields have the value +11:00 appended to them. This is a UTC formated date and the +11:00 represents my timezone. Yours may be different.
If we try to pass this XML into our “usp_UpdateCaseDetails” stored procedure, nothing gets updated as the structure of the XML has changed and our OPENXML statement does not recognize this structure. There are basically two ways to get our update stored procedure working again. I use Method 2 as it makes our stored procs easier to understand and because of another fix we need to implement to address the UTC datetime values (more on this in a while).
Method 1
Modify the stored procedure to include the namespace and a namespace alias (in this case dsCD). Notice that we have to revert to using the "SchemaDeclaration" value for the "with" argument.
-- *** Alter Update stored Procedure for Schema
ALTER PROCEDURE [dbo].[usp_UpdateCaseDetails] @dsXML as xml
AS
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT , @dsXML, '<root xmlns:dsCD="http://tempuri.org/dsCaseDetails.xsd"/>'
UPDATE CaseDetails
SET CaseName = [dsCD:CaseName],
RegistrationDate = [dsCD:RegistrationDate],
Notes = [dsCD:Notes],
DateLastModified = [dsCD:DateLastModified]
FROM OPENXML(@docHandle , '//dsCD:CaseDetails',2)
WITH ([dsCD:CaseID] INT, [dsCD:CaseName] VARCHAR(50), [dsCD:RegistrationDate] datetime, [dsCD:Notes] varchar(max), [dsCD:DateLastModified] datetime)
WHERE CaseDetails.CaseID = [dsCD:CaseID]
EXEC sp_xml_removedocument @docHandle
GO
Method 2
Add one line of code to your application to slightly modify the XML being passed back to the DB:
myXML = Replace(dsCaseDetails.GetXML, "xmlns=", "xmlns:" & dsCaseDetails.DataSetName & "=")
resulting in XML that looks like this:
<dsCaseDetails xmlns:dsCaseDetails="http://tempuri.org/dsCaseDetails.xsd">
<CaseDetails>
<CaseID>1</CaseID>
<CaseName>Start Here</CaseName>
<RegistrationDate>2006-05-09T00:00:00+11:00</RegistrationDate>
<Notes>Here we go</Notes>
<DateLastModified>2006-05-09T00:00:00+11:00</DateLastModified>
</CaseDetails>
</dsCaseDetails>
and modify the OPENXML lines of the stored procedure to look like this:
FROM OPENXML(@docHandle , N'/dsCaseDetails/CaseDetails',2)
WITH CaseDetails DataXML
WHERE CaseDetails.CaseID = @dsXML.value('(/dsCaseDetails/CaseDetails/CaseID)[1]', 'INT' )
That fixes the issue relating to the typed dataset, when you run the application and press on the save button, you will get the following error:
Msg 241, Level 16, State 1, Procedure usp_UpdateCaseDetails
Conversion failed when converting datetime from character string.
The first thing that comes to mind is that you must be using the wrong data to update your fields so you’ll spend countless hours trying to figure out what the heck is going on. Well, the cause of this problem is another one of Microsoft’s “it works that way by design” features.
In .Net 2.0, timezone information is appended to all datetime fields of a dataset. This is supposed to help with internationalization as the dataset will automagically adjust datetime values to reflect what the value should represent in a new timezone. Sounds like a great idea but the OPENXML function of SQL Server doesn't process this information, resulting in the above error. The strange thing is that SQL Server itself has no problem with this datetime format as it will allow you to copy one of the datetime values in the above XML and paste it directly into the relevant column of the table. Therefore, I assume this is a bug with the OPENXML function.
To get around this problem, I had to implement a Regular Expression in my code to strip out the timezone information so that OPENXML would parse the XML correctly. The code used was leveraged from an article written by Craig Geil's (How to fix DateTime values after .NET Xml Serialization) and modified to meet my requirement. The new function also implements the fix for the typed dataset issues raised above.
Private Function FixXML(ByVal ds As DataSet) As String
Dim fixedString As String
Dim sRegex As String
Dim xmlString As String
sRegex = "(?<DATE>\d{4}-\d{2}-\d{2})(?<TIME>T\d{2}:\d{2}:\d{2}\.{0,1}[\d]{0,3})[\d]*[/+-](?<HOUR>\d{2})(?<LAST>:\d{2})"
Dim r As Regex = New Regex(sRegex)
Dim myEvaluator As MatchEvaluator = New MatchEvaluator(AddressOf getHourOffset)
xmlString = ds.GetXml
fixedString = r.Replace(xmlString, myEvaluator)
fixedString = Replace(fixedString, "xmlns=", "xmlns:" & ds.DataSetName & "=")
Return fixedString
End Function
Private Function getHourOffset(ByVal m As Match) As String
Return m.Result("${DATE}" + "${TIME}")
End Function
Now if you run the application and save your changes again, you’ll see that the data is updated correctly. This code works just as well if you create an assembly and host it in SQL Server. All you need to do is call the function in your stored procedure just before you issue the "sp_xml_preparedocument" function. We've implemented it in our data access layer as we had concerns that most DBA's probably won't allow CLR to be enabled on their servers.
Conclusion
XML is a very poverful and flexible standard that can be used for all types of information exchange. In this instance, it is being used as a simple means of passing parameters to stored procedures.
In Part 2, I will show you how to use the same method to pass multiple rows of data to the database in one transaction; a process that not only reduces trips across the wire, but makes it a breeze to synchronise changes when your smart client application reconnects to it's source.
Cheers
Jeff
Champage
Home Time :-)
Wow that was a great Tech-Ed
