Get XML from SQL Server 2000

During the last two weeks I experimented a new (to me) way to get XML data from the RDBMS I'm actually using for this weblog.

But let's take a step back and see how my weblog actually generates these pages. This is one of the possible scenarios:

  1. User clicks a permalink and triggers a command, say message.m?id=999. The meaning of the command is pretty self-explanatory: retrieve the message with id #999 and show it.
  2. On the web server such command contains a bunch of SQL calls coded in C# language.
  3. SQL server returns to the command the row containing all the fields associated to the requested message (title, body, creation date, etc.) and system init a business object originaly named "Message" with all the retrieved data.
  4. The business object is serialized into an XML stream. Yes, you have some degree of control on how to structure the result XML.
  5. Via a XSLT file the XML representation of the business object is transformed to HTML 4.01 Strict page.

This process is very tedious. I could have used some workarounds to reduce coding but none of them would give me total control on generated XML. Since I was damn sick to get XML from SQL using this approach I started looking to the XML features bundled with Microsoft SQL Server 2000. I wish to have done before!

Basically the whole thing boils down to a single HTTP request coupled with a XML Schema file. Let's reuse the previous scenario and see what's changed:

  1. User clicks a permalink and triggers a command, say message.m?id=999.
  2. On the web server such command is associated to a URL. Such URL identify a XML Schema and, optionally, a template. Such files tell SQL how to filter rows from the rest of the data and convert them to XML. Internally IIS passes the schema and template files to SQL Server. When data has been retrieved and filtered SQL Server sends back an XML stream to the caller.
  3. Via a XSLT file the XML file is transformed to HTML 4.01 Strict page.

As you can see the coding work is drastically reduced since former step #3 is not required at all. Even better the C# code involved for the command is virtually the same for every scenario, hence it can be easily parametrized. Infact with the newer implementation for each command you can specify a different the URL to query SQL Server.

For the record I have a new page of archived messages running locally done in a matter of minutes using this new technique. It will be on-line soon (sooner than "About Me" link, I promise!). I could back on this topic when the archive page will be available and show how to map the XML and database tables columns.

You can find a lot of implementation details on the help pages installed with SQL Server, in particular check the category: "XML and Internet Support". The following links may also help:

Tutorial: XML Templates
Use of templates when querying SQL Server
SQL Server Home Page
Microsoft's supoort site for SQL Server.
SqlXml.org
News, articles of interest, a comprehensive FAQ section about XML support of SQL Server 2000.

Feb 21 2003, 11:28 AM

HTML 4.01 + CSS. Copyright © 2003 Andrea Peltrin