XML-ify your stored procedures

by Jimmy Nilsson.  Jimmy Nilsson is the owner of the Swedish consultant company JNSK AB http://www.jnsk.se/

Get the source code here!

Introduction

Since I'm a geek I love to use new technology. When XML was quite new a couple of years ago, I used it as the file format in a project for a customer of mine. Hey, I know what you think<g>, but my customer actually thought - and still thinks - it was a good solution to use XML!

As a matter of fact, my illusions were killed and it felt as being kicked back 10 years in time regarding the tool support. The project worked out fine finally, but I decided to be reluctant to XML for some time. What happened with XML and the tools after that you know a lot about since you as a VBXML-reader most probably are an XML-pro. The possibilities for XML, the standards and tool support have had a tremendous development, and I guess we are just in the beginning.

XML support in SQL Server 2000

My XML interest took a new start with SQL Server 2000. There is a lot of XML-support in SQL Server 2000, such as:

• Send SQL queries with URLs to SQL Server over HTTP
• Executing XPATH queries at URL
• Pass XML-documents to stored procedures and open them for further processing
• Fetch results from SELECT statements as XML documents

Not all of the XML support in SQL Server 2000 is useful in real world applications in my opinion. I'm thinking of the possibility to send queries to SQL Server by HTTP and receive the answer as XML documents. The security problems with that seem to be huge in the current version. (For example, your database schema will be more or less public to your users and they can easily learn to do similar things directly with your database, as you are doing with your "behind-the-scenes" HTTP calls.)

On the other hand, letting your stored procedures return and accept XML is a really nice thing to do. (I'm thinking about the last two bullets of above. I will also give examples of how that work later on in this article.) Even if you don't want to use it in production directly today, I think you should at least start to explore the technique.

BTW, the XML support is now native in SQL Server 2000, but you can download an evaluation preview of this XML support to test it with earlier versions of SQL Server at the MSDN website.

Motivation

Let's assume that your business rules components should return XML, then it's very efficient to let SQL Server take care of the job for that. It will also require very little code.

A common way of improving performance is to decrease the amounts of roundtrips. If you can send several commands or return several result sets in one call, you have typically gained a lot. It will, for example, lead to shorter transactions, and that is one of the most important factors for scalability.

Still, I think you should learn from my story above about being too eager to adopt new stuff. Remember that this is kind of version 1. Do also remember that your data tier is perhaps the hardest tier to scale out. By letting your SQL Server work with XML, you may take too much resources from it. That has somewhat changed with Distributed Partitioned Views in SQL Server 2000, but it's still hard to scale out since the management tools are really not there yet. (Distributed Partitioned Views let you split a table across two or more servers so that the servers will work in parallel with servicing requests to that particular table. To the clients, the table will look as one single table. To really benefit, you should add routing to your components before they hit the database servers. Oops, this topic is way out of scope for this article.)

Examples

Let's take a look at two examples. The examples will show how to send an XML document as a parameter to a stored procedure for further processing and the second example will return a rowset in the form of an XML document from another stored procedure.

The database I use for the examples looks like this:

The basic idea is that there are persons in the database and each person has zero, one or several status codes. A status code could be that the person is nice, another that he/she is hard working, not cooperative and so on. (In Sweden it will be very hard to get permission to store information like that in a database since it's sensitive for the personal integrity. Swedish authorities, please note that this is a fictional sample only<g>!)

The first example

In the first example, I want to INSERT a new person. I therefore create an XML document with one person and a couple of status codes for that person. (The XML document is created in a script to test the stored procedure. In a more realistic situation, the XML document would be created by VB Script code, for example, because of a request to an ASP page.) I then send the XML document as a parameter to a stored procedure for inserting the information to two different tables.

SET @strXML =

'<root>

  <person id="3" firstname="Tim"

  lastname="Nilsson" address="Sweden">

    <personstatus status="1"/>

    <personstatus status="2"/>

    <personstatus status="8"/>

  </person>

</root>'

EXEC @intErr = Demo_XML1 @strXML

And the most important part of the Demo_XML1 stored procedure looks like this (one snippet, with some comments, at a time):

CREATE PROCEDURE Demo_XML1 (@vstrXML VARCHAR(8000))AS

  DECLARE

  @intErr INT,

  @intRowcount INT,

  @intDoc INT,

  @bolOpen BIT

  SET NOCOUNT ON

  SET @intErr = 0

  SET @bolOpen = 0

  EXEC sp_xml_preparedocument @intDoc OUTPUT, @vstrXML

  SET @intErr = @@ERROR

  IF @intErr <> 0 BEGIN

    SET @intErr = 1

    GOTO ExitHandler

  END

  SET @bolOpen = 1

(If you wonder why the structure of the stored procedure is as it is, read my article called "Standardize your code structure for T-SQL stored procedures". There you will also get tips for how to take care of tracing, error logging and more.)

The reason for why I use @bolOpen the way I do, is that I need to know when it's time for the clean up code if the document where opened or not. I should not try to close if it wasn't opened. Otherwise I get an error.

Above sp_xml_preparedocument was called to parse the XML document that was sent in, and to instantiate an object model.

  INSERT INTO person (id, firstname, lastname,address)

  SELECT * FROM OPENXML(@intDoc,

  '/root/person')

  WITH (id INT '@id',

  firstname VARCHAR(50) '@firstname',

  lastname VARCHAR(50) '@lastname',

  address VARCHAR(50) '@address')

Above I search the XML document from the /root/person node. The found person (or persons if there were several in the document) will then be inserted to the person table.

  SELECT @intErr = @@ERROR, @intRowcount = @@ROWCOUNT

  IF @intErr <> 0 OR @intRowcount = 0 BEGIN

    SET @intErr = 2

    GOTO ExitHandler

  END

  INSERT INTO personstatus

  SELECT * FROM OPENXML(@intDoc,

  '/root/person/personstatus')

  WITH (id INT '../@id',

  status INT '@status')

Once again the XML document is searched, but this time only for status codes. All status codes will be inserted to the personstatus table.

  SELECT @intErr = @@ERROR, @intRowcount = @@ROWCOUNT

  IF @intErr <> 0 OR @intRowcount = 0 BEGIN

    SET @intErr = 3

    GOTO ExitHandler

  END

ExitHandler:

  IF @bolOpen = 1 BEGIN

    EXEC sp_xml_removedocument @intDoc

  END

  RETURN @intErr

Finally, the XML document is removed again so we free up resources.

The second example

In the second example I will SELECT with a join from the database and ask to get the output as XML. The following SELECT statement could be used in a stored procedure:

  SELECT person.id, person.firstname,

  person.lastname, personstatus.status

  FROM person INNER JOIN personstatus ON

  person.id = personstatus.person_id

  FOR XML AUTO

The person table and personstatus tables are joined. Nothing rocket science there, right? Since I add FOR XML AUTO at last, I will get the output in XML format. The XML document will look like this when I add <root> </root> and reformat it for readability (or save it as an XML file and open it in IE):

<root>

<person id="1" firstname="Jimmy" lastname="Nilsson">

 <personstatusstatus="1" />

 <personstatusstatus="2" />

 <personstatusstatus="8" />

</person>

<person id="2" firstname="John" lastname="Doe">

 <personstatusstatus="4" />

 <personstatusstatus="8" />

 <personstatusstatus="16" />

</person>

</root>

What is worth mentioning is that the output isn't as you expect from a join. Instead you get it in a hierarchical way. First one person with his status codes, then another person with his status codes and so on.

FOR XML AUTO is only one way of describing how you like to format the result. Let's shortly discuss two more of several other possibilities. If you use FOR XML RAW instead, you will have each row in the resultset as a node called "row" and each column as an attribute. FOR XML EXPLICIT will require more work, but gives a better chance to get exactly the output you want. The technique isn't very easy and direct, but it's possible to use.

What about transactions?

OK, XML may solve several problems automagically, but when it comes to transactions, we have to take care of that on our own. The reason that I didn't show it above was that I didn't want to clutter the code from what I really wanted to show. We could also imagine that a COM+ component was taking care of the transaction in my example.

Need more information?

Sure you do! The examples and explanations I gave you above were just small appetizers. You need to read up on the details and the other variants than what I showed. A good place to start at is in, for example, Books On-Line (BOL) for SQL Server 2000 or SQL Server and XML Support.

Coming soon

I guess a lot will happen with the XML support in SQL Server in the future. What I know about today is that there soon will be a web release for something called updategrams. Right now you can download a beta version of that web release that will give you this support. (See references below for more information.) The idea behind updategrams is that you should be able to describe statements for UPDATE, DELETE and INSERT in XML-documents and have those statements to be processed by SQL Server, without having to do work similar (and most often much more complicated) to what I showed in the first example above.

Another thing that I think will be very interesting is of course the XML support in VS.NET and ADO.NET, but that is a long story and out of the scope for this article.

Conclusion

I think there is some "first version" feeling in the XML support in SQL Server 2000, but it still shows a lot of promises. Try it out and create your own opinion. It's really easy to get started and this article gave you a couple of ideas for where and how to start.

References

• The supplemental code for the article can be found here.
• Download the (very big!) SQL Server 2000 Evaluation version here

• When I wrote this article, the updategram feature was to be found in the web release beta called: " XML for SQL Server 2000 Web Release 1 Beta 2"

• The following link about the XML support in SQL Server is a good place to start reading more about what was discussed in the article: "SQL Server and XML Support"

• My article at ASPToday about code structure for stored procedures: "Standardize your code structure for T-SQL stored procedures" (You may have to pay a small fee for an ASPToday subscription to read it.)

• Also try the SQL Server 2000 subweb at TopXML by James Speer

---

Jimmy Nilsson is the owner of the Swedish consultant company JNSK AB http://www.jnsk.se/ has been working with system development for more than twelve years (with VB since version 1.0) and, in recent years, he has specialized in component-based development, mostly in the Microsoft environment. He has also been developing and presenting courses in database design, object-oriented design, etc. at a Swedish University for six years. Reach him at Jimmy.Nilsson@jnsk.se.