With this download, Josh Sullivan b the winner of our VBeXpress competition – demonstrates how his easy-to-use class makes it a breeze to work with the XML features of SQL Server 2000.
He told us: "Recently I have been working with SQL Server 2000 and its XML returning features, mostly within an ASP environment. I found myself writing many different pages that display table-like and hierarchical data. I also found myself writing repetitive code in every page, mostly ADO code and the error handling involved with it. So I wrote this class to save myself some coding. 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. We can do this by using the setting the ADO Connection’s CursorLocation to adUseClient.
"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 XML could also be sent to the client (IE only) and the XSL applied on the client side to free up Server Resources.
"This example uses 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.
"I currently have this class compiled in an ActiveX DLL that supports COM+ transactions. I use it from many of my ASP pages in combination with XSL to create quick data driven pages. I could also be added to any Visual Basic Project that needs to work with XML from SQL server 2000.
"If anyone has any suggestions on optimising it, improved error handling or anything else, I would love to hear their idea’s. I can be reached at [email protected]"