An Alternative XML Solution

Introduction

XML is a great tool for sharing and manipulating data. It is becoming (or is already!) a de facto standard in the Enterprise for the sharing of data in the form of XML documents, saved as .XML files (which can be opened and viewed in Internet Explorer) or imported directly into SQL Server. SQL Server 2000 introduced functionality for XML in the form of the FOR XML clause, which you can use in your SELECT statements. This is used to convert your queries into XML. However, the reality for SQL Server 2000 users is not quite so simple.

The Problem

The first problem with the FOR XML clause is that it does not create a well-formed XML document, in other words you can't just save the result of your query as a XML file and expect to be able to open it in Internet Explorer. You have to programmatically append ROOT tags at the beginning and end of your document. In addition to this, if used from BCP or Query Analyser, the XML result set is split over several rows. This may not seem too bad, until you realize that this splitting of rows MAY have chopped a tag in half - this will cause an error when you open the XML file in Internet Explorer. You will have to manually or programmatically string the lines in the resultset into one line. If you use another method to retrieve results (HTTP access, stream object in ADO), you shouldn't have this problem - but how do you plug an ADO solution into SQL Server unless you use ActiveX scripting in a DTS?

There are many possible solutions

An article called 'A Survey of Microsoft SQL Server 2000 XML Features' published by Microsoft in July 2001 (recently published on SQLServerCentral.com) documents various XML solutions. Some of these are simple, some are complex. Unfortunately, while these solutions are really powerful, most of them require ADO programming, and a lot of knowledge of XML - not so good for a newbie or someone who needs to put out a quick solution.

The easiest solution is of course to wait for SQL Server 2005 to be released. The new version of SQL Server has introduced impressive XML functionality in the Beta versions. If you don't want to wait, however, you could use an ActiveX script in a DTS package to prepare an XML document. This is very easy to code, and offers great flexibility but you will need to code a separate lengthy script for each XML file you need to generate. Or you could use the same script and call the ADO Stream object to parse an XML Template - but again, you must do this in a DTS package. The system sproc sp_makewebtask can also create XML documents in the form of a HTML document. This is useful if you want to view your file on the Internet, but not for creating files.

Another way (we're getting closer now!) is to write a VB or C# program and use the Save method that is part of the RecordSet object in ADO. This method will save an ADO resultset as a XML document. Information on how to accomplish this can be found on the Internet in many places. Unfortunately, the ADO resultset is a denormalised resultset (i.e. no nesting - which is useful in XML documents), and you have no control over the layout of the XML document - unless you build in functionality for stylesheets. I wanted a more flexible, simple solution (i.e. I was too lazy to learn stylesheets!). I found it with XML Templates. The FOR XML clause offers great flexibility and variation to your XML documents, but what if you want to combine several queries into one document? XML Templates offers this solution. You prepare a template, which is an actual XML document, and place your SQL queries in the document. Once again though I was trapped by the need to write ADO code in an ActiveX script - i.e. DTS.

There is functionality embedded in SQLXML (included in SQL Server 2000 Web Release, and available for download at SqlXml 3.0 SP2 - make sure you download version 3 SP2), MSXML 4.0 SP2 (also downloadable from Microsoft), and ADO.NET enabling you to programmatically apply a XML Template to a database without any security risk. You can format the XML Template to meet any requirement, and embed as many queries and scripts as you need. The result is saved to a XML file, which contains your formatting and the data returned from the queries. If you don't want to use embedded SQL, put the SQL in a stored procedure (the sproc must return a resultset and must use FOR XML) and execute the sproc from your Template. You can even code your XML Templates with parameters, which can be passed to your queries. To add more flexibility you can save the XML Template in a table in text format instead of having to deal with Dos files. Using the power of XML Templates, you can extend your functionality even further by specifying an XSD mapping schema and an XPath query instead of using SQL Queries in the Template.

To quote Microsoft:

"We feel that the use of templates with XSD mapping schemas and XPath queries is the most powerful

mechanism for getting XML-based data directly from SQL Server 2000."

- http://msdn.microsoft.com/msdnmag/issues/02/01/SQLXML/default.aspx

XML Templates is a productive alternative to the XML newbie. My next requirement was to be able to execute this XML Template from anywhere within SQL Server, and to re-use this solution with minimal effort. I also wanted to option of bypassing the template by executing a simple query string using FOR XML and have this converted into an XML file.

To achive this, I coded all this functionality into an extended stored procedure. You can call the extended stored procedure from anywhere in SQL, passing the server and database to connect to, the name of the resultant XML File, and the ID key to the table containing the XML Template you want to process. You can also pass up to 10 parameters, which can be used in the template. Instead of passing the ID of the XML Template, passing a SQL query string will cause the resultset to be written to a XML file.

To code an extended stored procedure, you need to use Delphi or C++ (I used Visual C++.NET). Visual Studio .NET provides a template for creating extended stored procedures, and if you navigate to 'C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\ods' you will find a self-extracting ZIP containing samples on how to create extended stored procedures. The readme document in the .NET project will give you some guidelines. You should be able to use native C++ to create an extended stored procedure as this functionality has been around for a while.

Once the code is completed, build the DLL and save it to 'c:\Program Files\Microsoft SQL Server\80\Tools\Binn'. Use sp_addextendedproc to add the extended proc to your database. You do NOT need to register the DLL in the usual way (i.e. using regsvr32).

Creating an Extended Stored Procedure using VC++.NET

The first step is to use Visual C++.NET to create the basic extended stored procedure, which accepts parameters when called from SQL Query Analyzer.

Install SQLXML, MSXML, .NET Framework and .NET Framework SDK and open a VC++.NET project using the Extended stored procedure template.

Add the following references:

• System.XML
• System.Data
• System
• mscorlib
• Microsoft.Data.SqlXml

and set the project properties to 'Use Managed Extensions'. Here is the code - it is not too long.

NOTE : I don't pretend to be a C++ expert, but I have had an expert QA this code, and have used it fairly extensively. Use at your own risk! If you are unfamiliar with C++, read the comments in the code - it should be clear enough, but please feel free to contact me if you require any clarification. Start reading from function 'xp_XMLTemplate_WriteFile' near the end of the code. This function calls class 'CXTWFile' which does most of the work.

Download alternativexml.cpp

Once the code has been compiled, copy the DLL to 'c:\Program Files\Microsoft SQL Server\80\Tools\Binn'

Create the extended stored procedure using:

master..sp_addextendedproc 'xp_XMLTemplate_WriteFile', 'XMLTemplate.dll'

Drop the extended stored procedure using:

master..sp_dropextendedproc 'xp_XMLTemplate_WriteFile'

The documentation says you are supposed to use 'DBCC xp_XMLTemplate_WriteFile(FREE)' to release the DLL so you can replace it, but I couldn't get this to work. I had to stop and start SQL to free the DLL

Next, create your table to hold the template and insert a template into the table:

CREATE TABLE [dbo].[XMLTemplate] (
[XMLTemplateID] [int] IDENTITY (1, 1) NOT NULL ,
[XMLTemplateDate] [datetime] NOT NULL ,
[XMLTemplate] [varchar] (8000) NOT NULL 
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[XMLTemplate] WITH NOCHECK ADD 
CONSTRAINT [PK_XMLTemplate_XMLTemplateID] PRIMARY KEY  CLUSTERED 
(
[XMLTemplateID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[XMLTemplate] ADD 
CONSTRAINT [DF_XMLTemplate_XMLTemplateDate] DEFAULT (getdate()) FOR [XMLTemplateDate]
GO

Note that the stored procedure should return a resultset and must use the FOR XML clause.

INSERT INTO XMLTemplate (XMLTemplate)
VALUES (
'<XMLTemplateFile xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
<sql:param name="Param1"/>
<sql:param name="Param2"/>
<sql:param name="Param3"/>
<sql:param name="Param4"/>
<sql:param name="Param5"/>
<sql:param name="Param6"/>
<sql:param name="Param7"/>
<sql:param name="Param8"/>
<sql:param name="Param9"/>
<sql:param name="Param10"/>
</sql:header>
<Param1 xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT @Param1
</sql:query>
</Param1>
<Param2 xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT @Param2
</sql:query>
</Param2>
<Part1 xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
EXEC sproc @Param1 = @Param1, @Param2 = @Param2
</sql:query>
</Part1>
<Part2 xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT * FROM abc WHERE xyz = @Param3 FOR XML AUTO
</sql:query>
</Part2>
</XMLTemplateFile>')

Create your XML file as follows:

EXEC master.dbo.xp_XMLTemplate_WriteFile
@XMLFileName= 'C:\testfile.XML',
@XMLInput = '1',
@Server = '(local)',
@Database = 'DatabaseName',
@Param1 = @Param1,
@Param2 = @Param2,
@Param3 = @Param3,
@Param4 = ' ',
@Param5 = ' ',
@Param6 = ' ',
@Param7 = ' ',
@Param8 = ' ',
@Param9 = ' ',
@Param10 = ' '

If you don't want to use an XML Template, run the following:

EXEC master.dbo.xp_XMLTemplate_WriteFile
@XMLFileName= 'C:\testfile.XML',
@XMLInput = 'SELECT * FROM ABC WHERE xyz = 'ABC' FOR XML AUTO',
@Server = '(local)',
@Database = 'DatabaseName',
@Param1 = ' ',
@Param2 = ' ',
@Param3 = ' ',
@Param4 = ' ',
@Param5 = ' ',
@Param6 = ' ',
@Param7 = ' ',
@Param8 = ' ',
@Param9 = ' ',
@Param10 = ' '

Conclusions

This solution may be difficult to maintain, as it is written in C++, but it will offer great flexibility to your enterprise. By simple coding a new query, you can create a new XML file. Using the more advanced aspects of FOR XML, you can create impressive XML documents.

Knowing how to write your own Extended Stored Procedures is a great advantage to the SQL programmer, as it offers the ability to extend the functionality of SQL Server 2000.

Wayne Fillis @ SoftwareFutures

contact me at: wayne.fillis@cpt.softwarefutures.com