This article describes using some of the new XML

functionality provided by MS SQL Server 2005 as applied to argument passing to

T-SQL stored procedures. One of the more tedious areas of application support

is managing arguments passed to and from T-SQL stored procedures. Unlike other

RDBMS engines such as Oracle, T-SQL does not support the notion of passing in

record structures as arguments from a client directly to a T-SQL stored procedure. Realizing that the new XML functionality might

offer some relief in this area, I did some searching to see what anyone else

had come up with. I found some articles related to XML, but the articles I did

find used SQL 2000 and had the primary focus of using XML for bulk Inserts with

no preliminary data validations or other processing steps (see References

below). As I had some different objectives in mind, and I found no articles

highlighting the new SQL 2005 XML features, I decided to present my ideas and

results here.

Background

The use of MS SQL Server as a web back-end storage system is

well understood. Maintaining the stored procedures that interact with web

clients can be tedious, with the lack of record structure support to and from

stored procedures a contributing factor. There are several consequences to

this, including:

• Commonly used procedures have multiple copies created with

  slightly different arguments

• There are many instances where passing numerous arguments

  and/or varying number of arguments to stored procedures can be awkward or

  nearly impossible

• Altering the arguments to a commonly used procedure can be

  a high-maintenance task any dependent code snippets must be changed in

  parallel

• T-SQL does not include a formal mechanism to pass in

  record structures from a client application, much less support passing in

  a varying number of records

Transaction support is another labor intensive effort: In

a web back end, the database context, or session, only exists as long as that

one procedure call is active. Thus, a transaction that requires multiple

procedure calls requires some way to wrap the transaction/rollback around the multiple

invocations. For example, storing an e-commerce transaction can involve a

dozen tables and multiple, varying number of rows per table (e.g. sales line

items) which is really impossible to pack into standard procedure call

arguments. Some people use this difficulty as a reason to embed SQL in their

application code instead of placing it into a stored procedures.

Another interesting case arises when you want to send a stored

procedure the minimal number of columns in a row to update. First, we have the

issue of variable number of arguments (which columns are getting updated?);

next, we are faced with future maintenance issues as columns are added to (or

deleted from) the table; then the update code has to decipher a tri-state

assertion for each column in the table: Do we leave the column alone, update

it to a value, or update it to Null? Traditional argument calling sequences

might involve two arguments per column, one to pass the new value, and an

indicator variable to say if we are really updating or not - not pretty. The

open structure of XML lets us define such things easily.

Finally, there is the reverse interface issue of sending

back to the calling program multiple values, possibly structured; for this

discussion I will use error messages. Suppose we have a row insert procedure,

and we do a number of validations on the column values before executing the

Insert command. What we do not want to do is bail out after detecting the

first error, go back to the user with one error, they fix it and resubmit, we

bail out on the next error, etc. until the user gets dizzy. We would like to

do all our data validations and pass back all the conditions and messages in

one pass, so the user can fix them in one try. Here again we need to pass

multiple results (independent of a dataset), which the basic T-SQL Procedure

argument list makes difficult if not impossible.

Thus the issues at hand are:

• Number of arguments (complexity), and maintenance issues

  (add/delete arguments)

• Tendency to create multiple copies of the same procedure

  for slightly varying input conditions

• Passing a record structure
• Passing a varying number of multiple records
• Adding processing criteria to arguments (e.g. no update;

  update with value; update to Null)

• Pass back a varying number of (potentially structured)

  values, e.g. error messages.

XML As A Solution

T-SQL has native support for manipulating XML in SQL 2000,

but with the release of SQL 2005, XML support is even better. T-SQL arrives

with additional functions and an actual XML native datatype [essentially an

nvarchar(max) with storage in "optimised UTF-16 characters" per BOL].

There are some curious comments in the BOL about the overhead of XML

processing, but I have not yet tried to measure it. My general rule is, if a

certain syntax or process makes for increases in programming efficiency and

reliability as well as reducing long term maintenance, I care less about a

little additional overhead on the machine.

Client Code

On the application (e.g. web page) side, instantiating the

MSXML object to create an XML document certainly has overhead, especially as it

supports a lot of esoteric XML syntax that may be seldom used. With the new

SQL Server 2005 enhancements, some of the client-side processing can be

entirely relieved: XML arguments can be treated as a string and passed to a stored

procedure without any need for complicated objects to help us. You should certainly

use the MSXML object if it fills other requirements the client-side application

needs. Note: If you do not use the MSXML object, then you need to handle the

translation of reserved characters into their alternate representations, a.k.a.

'entities' . For example the XML reserved character "<" [less

than sign/open angle bracket] in user data must be replaced with the XML Entity

"&lt;". The same

holds for ">", "'", """, and

"&". You could write a small function to handle those [hint:

process "&" first].

Using XML makes the calling convention to stored procedures very

simple: As the XML is by its nature structured, when we extract values out

of the XML from inside the procedure, the code need make no assumptions about

ordering of individual data items; handling data element existence or non-existence

is also handled gracefully. The presence of possibly obsolete data items is no

longer a bother to the stored procedure - it simply ignores them. All of this

helps reduce maintenance.

Web Server Application Code

We utilize the fact that an XML document can be represented

by a character string, and simply append and build it. Note, if you really

wanted, you could instantiate the MSXML object and build your XML with that,

but this seems far too much for the job at hand. My presumption is that all of

the user input has been collected and validated to some extent by the application

program, and is then to be passed to the database as a single transaction.

Example: Insert Customer Row [vbscript]

xcmd = "<arg><first_name>" & firstName & "</first_name><last_name>" & lastName &_
"</lastname></arg>"

where arg is the root tag for the whole document, and

firstName, lastName etc. are the variables holding the user input. If a data

item is missing, the stored procedure can fill in a default or raise an error,

depending on the business rules involved.

Example: Update Customer Row [vbscript]

xcmd = "<arg><customerid>74285</customerid><first_name>Robert</first_name>" &_
 "<company_name null='yes'/></arg>"

This could be sent to an Update procedure. It provides a

primary key, a new first name, and an explicit instruction to set the company

name to Null; all other columns are unmodified.

Example: Create Sales Transaction [vbscript]

xcmd = "<arg><customer><first_name>Mary</first_name>. . . .</customer>" &_
 "<order_header><order_date>08/15/2006</order_date>. . . .</order_header>" &_
 "<order_lines count='6'><line productid='7294' . . .>" &_
      "<line productid='8241' . . .>. . . .</order_lines>" &_
 "<payment><type>Visa</type>. . . .</payment></arg>"

Here we consolidate multiple record types, and multiple

records for sales line items, all into one procedure call. This can then

easily be wrapped in a single Transaction in case of unexpected database

errors. The line count is optional, but if you know it then why not send it

along, the procedure can make use of it. Otherwise the procedure will have to

loop on searches for order_lines until it runs out of them. Remember that in

most cases the non-significant white space (blanks, new lines, etc. in between

elements) are ignored and often disappear when the XML document is stored in a

canonical form, such as an XML variable.

I have written web page scripts that do just this kind of

processing. First you call a procedure to add the customer. Then call another

procedure to add the order header. Then a big loop call to insert each sale

line item. Yet another procedure to post the payment. And if one of these

burps along the way? Go write another procedure to try to clean up a

half-baked transaction! That kind of processing is where this interface really

shines.

Example: Error Message Return Results

<err>
 <error>
 <err_code>APP001</err_code><err_msg>First Name is a required field.</err_msg>
 </error>
 <error>
 <err_code>APP002</err_code><err_msg>Last Name is a required field.</err_msg>
 </error>
</err>

This might be returned by a procedure call. Multiple

messages can easily be handled, as well as a record structure (shown here)

composed of both an error code as well as the error text message. Successful

execution is indicated by simply returning Null.

Stored Procedure Interface and Code

The calling program (a server-side web page script, for our

examples) makes a standard procedure call, passing the string that has been

built as an input XML argument. plus an output XML argument for the error

status return. The variable types could typically be double-wide character,

e.g. Unicode.

Here is an excerpt of a stored procedure to validate values

then Insert a customer row.

/************************************************************************************
file is xtest.sql Copyright (c) 2006 Jack A. Hummer
*/
CREATE PROCEDURE [dbo].[xtest]
/*
Purpose
 Example procedure demonstrating XML input and output arguments.
 Processing to validate and insert a Customer row.
Input */
@xin
XML, /* input values for columns
 <arg>
 <fname>...</fname> First Name, required
 <lname>...</lname> Last Name, required
 </arg>
 Missing fields, or fields passed as <xxxx
null="yes"/> are considered
 Null and will have defaults applied as needed.
Output */
@id_customer
INT = NULL OUTPUT, -- new primary key
@xout
XML = NULL OUTPUT /* error message(s), or Null = good return
 <err>

<error><err_code>nnn</err_code><err_msg>xxxxxxx</err_msg></error>
 </err>
Notes
 
Revision History
14aug06 jah created
************************************************************************************/AS
DECLARE @first_name NVARCHAR(50), @last_name NVARCHAR(50), @nvl VARCHAR(3)
SET NOCOUNT ON;
SET @xout = NULL -- default is good return
 
-- process first name - required field
SET @nvl = @xin.value('(/arg/fname/@null)[1]',
'NVARCHAR(3)')
SET @first_name = @xin.value('(/arg/fname)[1]', 'NVARCHAR(50)')
IF @nvl = 'yes' OR @first_name IS NULL BEGIN
SET @xout = dbo.xerr(@xout, 'E001', 'First Name cannot be Null')
END
-- process last name - required field
SET @nvl = @xin.value('(/arg/lname/@null)[1]',
'NVARCHAR(3)')
SET @last_name = @xin.value('(/arg/lname)[1]', 'NVARCHAR(50)')
IF @nvl = 'yes' OR @last_name IS NULL BEGIN
SET @xout = dbo.xerr(@xout, 'E002', 'Last Name cannot be Null')
END
-- more field validations.....
-- Finished field validations
IF @xout IS NOT NULL RETURN
-- continue processing.....
-- A Try/Catch around other statements could also append an error.
RETURN

For convenience as well as modularity, I created a small

T-SQL function to insert one error record into the XML error document. This

will get enhanced in the future, for example returning the error message in the

users language, or optionally writing it to a log file.

/************************************************************************************
file is xerr.sql Copyright (c) 2006 Jack A. Hummer
*/
CREATE FUNCTION [dbo].[xerr]
/*
Purpose
 Example function demonstrating XML error arguments.
 Add one error record to the XML error structure.
Input */
(@xmsg XML, -- current error msg(s), or Null
@err_code
NVARCHAR(20), -- new error code
@err_msg
NVARCHAR(1000) -- new error message
)
-- Output
RETURNS XML /* updated document
 <err>

<error><err_code>nnn</err_code><err_msg>xxxxxxx</err_msg></error>
 </err>
Notes
 
Revision History
14aug06 jah created
************************************************************************************/
AS
BEGIN
 
-- Initialize the document if needed
IF @xmsg IS NULL SET @xmsg = '<err></err>'
-- Insert this error sequentially after any others
SET @xmsg.modify(' 
insert (
 <error>

<err_code>{sql:variable("@err_code")}</err_code>,

<err_msg>{sql:variable("@err_msg")}</err_msg>
 </error>
 )
as last into (/err)[1] ')
-- Return the result of the function
RETURN @xmsg
END

A few notes on this code and related programming issues:

• The XML data type acts very much like an object, a new

  twist for T-SQL. It is also very flexible, as it is valid as a variable,

  argument, or function return. However, from ASP, ADO did not like it as

  an output variable type.

• Many of the new methods/arguments require lower case only,

  an abrupt change from most vendors SQL syntax which has usually been case

  insensitive. Not my preference but, hey, they forgot to ask me.

• The .value method utilizes XQuery syntax. If you have not

  seen this before, it is a complex language used to find things inside an

  XML document. Don't try to learn it from BOL, look for an introductory

  tutorial on the web (but, most of the introductory material I found progressed

  very quickly into advanced language features without completely explaining

  the fundamentals).

• You will need to experiment a bit to see what happens when

  you search for element values or attribute values and they do or do not

  exist.

• The .modify method uses both standard XQuery plus new

  extensions that Microsoft had to invent. The standards people have not

  yet addressed XML update syntax. Re-read the BOL articles a few times

  until it starts to make sense. In particular, the first argument must

  litterally be a string litteral, which seems rather limiting at first,

  unless you manage to stumble across the "sql:variable()"

  work-around.

• Contrary to popular belief, apostrophes (a.k.a. single

  quote) may optionally be used around XML attribute values.

• The .value method returns the reserved entities back into

  their original characters.

• Within T-SQL, CAST or CONVERT an XML variable to a

  character string type leaves the special entities intact.

• The .value method will right-trim the return string

  according to the data type specification, with no error.

Special Note: when putting together these ideas into some

real production code, I found the procedure return argument as an XML data type

did not work coming back through ADO to vbScript, so I changed it to an

NVARCHAR. This worked just fine when loading it into an MSXML object. The

.Net infrastructure may provide enhanced support for passing XML.

Stored Procedure - XML Code Loop

So you may ask, How do I exactly process an unknown number

of records in the input XML structure? Given the restrictive syntax on the

built-in XML methods, the only solution I have come up with is sp_executesql.

You will customize this for your situation, but here is a small example:

SET @n = 1
SET @params = N'@x XML, @value INT OUTPUT'
WHILE 1 = 1
 BEGIN
  SET @cmd = N'SET @value = @x.value(''(/arg/cat)[' +    CAST(@n AS NVARCHAR(2)) + ']'', ''INT'')'
  EXECUTE sp_executesql @cmd, @params, @x = @xarg, @value = @id_cat OUTPUT 
  IF @id_cat IS NULL
   BREAK
   -- do some processing
 SET @n = @n + 1
END -- while

Example: ASP Calls the Stored Procedure [vbscript]

Dim xin, xout,id_customer, conn, sqlcmd
xin = "<arg><fname>Mary</fname></arg>"
 
Set conn = Server.CreateObject("ADODB.Connection")
Set sqlcmd = Server.CreateObject("ADODB.Command")
conn.Open dsn
sqlcmd.ActiveConnection = conn
sqlcmd.CommandText = "dbo.xtest"
sqlcmd.CommandType = adCmdStoredProc
sqlcmd.Parameters.Append
sqlcmd.CreateParameter _ 
 ("@xin", adVarChar, adParamInput, 1000, xin)
sqlcmd.Parameters.Append
sqlcmd.CreateParameter _
 ("@id_customer", adInteger, adParamOutput)
sqlcmd.Parameters.Append
sqlcmd.CreateParameter _
 ("@xout", adVarWChar, adParamOutput, 2000)
sqlcmd.Prepared = True
sqlcmd.CommandTimeout = 40
sqlcmd.Execute , , adExecuteNoRecords
xout = sqlcmd.Parameters("@xout")
If Not IsNull(xout) Then
 Dim oDoc, loadOk, oNodes, oNode, errCode, errMsg
 Set oDoc = Server.CreateObject("MSXML2.DOMDocument.4.0")
 oDoc.async = False
 loadOk = oDoc.loadXML(xout)
 Set oNodes = oDoc.selectNodes("/err/error")
 For Each oNode In oNodes
 Response.Write oNode.nodeType & "<br>"
 errCode = oNode.selectSingleNode("err_code").nodeTypedValue
 errMsg = oNode.selectSingleNode("err_msg").nodeTypedValue
Response.Write("<tr><td>Error Code: " & errCode & "</td><td>" &_
 errMsg & "</td></tr>" & vbCrLf)
 Next
 Set oNode = Nothing
 Set oNodes = Nothing
 Set oDoc = Nothing 
End If

The above is a very simple example, but demonstrates

everything we have discussed. The error display to the web page could be

wrapped in a small function, and as an alternative could be handled nicely with

some XSLT code as well.

What About Schemas?

Yes, for added data validation you could add schema

definitions. But for something as transient as argument passing, it seems like

too much extra overhead. But for some very critical processing it may be

appropriate.

Speaking of Overhead

The extra overhead of converting native data types into XML

and back, as well as the overhead of instantiating the MSXML object seems to be

the only noticable downside to this approach. For me, the added capability of

passing variable amounts of data plus any reduction in long term maintenance

makes it a winner. But probably no worse than the creation of a recordset to return

structured data. Certainly many if not most procedure calls will not need

complex variable input arguments, and the XML output processing is only invoked

if there is an error, presumably only a small per centage of the time.

In Conclusion

MS SQL Server T-SQL does not provide a robust interface for

passing in varying numbers of arguments or any kind of record structure. On

output we may also desire a logical multiple record structure, separate from a

data recordset. And the more arguments a procedure takes, the bigger the

maintenance chore.

An XML input argument provides a simple way to send from

application code a variable number of arguments, including arbitrarily complex

logical record structures. An XML output argument likewise is a good container

for multiple return values, again including optional record structures. Since

the stored procedure just ignores any input elements it does not need, and can

often provide defaults for elements not passed, you have a greater chance of

being able to make changes to the stored procedure and/or underlying database

structure and have relatively little or no impact on existing programs that use

it.

References:

These all reference SQL 2000, and mostly use the XML input

to just do a direct bulk Insert without preliminary data validations.

http://www.devx.com/dotnet/Article/16155/0/page/2

http://www.sql-server-performance.com/jb_openxml.asp

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/

http://www.devx.com/dotnet/Article/16032/1954?pf=true

A Microsoft article on XML functionality overview in SQL Server, emphasis on SQL 2005.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xmloptions.asp

Introduction to XQuery in SQL Server 2005

http://msdn.microsoft.com/SQL/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5_xqueryintro.asp