Encapsulator of XML Features in SQL Server 2000

Josh Sullivan

Version Compatibility: Visual Basic 6

More information:
This class acts as a re-useable object for calling Stored Procedures and SQL Queries that return XML from SQL Server 2000.

With a few lines of code, you can return XML from SQL Server to your application. You can pass multiple input parameters to the stored procedure as well as output parameters for returning singular data. It also lets you easily access @RETURN_VALUE parameters from your Stored Procedures so that business rules can be applied within the stored procedure. You can call multiple stored procedures and Queries with while sharing one connection. This class does all these things and hides the details from the programmer.

I have included a Visual Basic Project with this class, the project can be used to return straight XML to the client application. The project can retrieve XML from SQL Server via a Stored Procedure or an SQL statement with the 'FOR XML [AUTO|RAW|EXPLICIT]' statement at the end.

I have also included an ASP page (CallSP.asp) and an XSL file (Products.xsl) to show an example of using the class from within ASP. The ASP page calls a stored procedure that has an input parameter and an output parameter. After the XML is returned to the page, we apply an XSL style sheet to transform it into straight HTML on the server using the MSXML2.DOMDocument object.

The examples use the Northwind Database that ships with SQL Server 2000. I also sent along a short sql script (SQL_Setup.sql) to create several stored procedure examples. The proper permissions must be set in SQL to view or execute the stored procedures from the Visual Basic Project as well as from an ASP Page.

Instructions: Click the link below to download the code. Select 'Save' from the IE popup dialog. Once downloaded, open the .zip file from your local drive using WinZip or a comparable program to view the contents.

Download sql2xml.zip