SQL Server Outputs an XML Stream

I just finished a project in which my company did some things with XML and Extensible Style Language Transformations (XSLT) that no one on the team had done before. The task at hand was to convert some existing Cold Fusion reports to Active Server Pages (ASP) reports.

The initial plan was to create dynamic SQL queries on the page, access the data through ActiveX Data Objects (ADO), return a recordset, and use HTML and Dynamic HTML (DHTML) to present the data. After the analysis phase, someone on the team started talking about what could we do to enhance this project and make it more scalable for the customer. The team decided that XML would be a perfect solution for presenting the data more flexibly, thus providing the application with a basis for potential growth and scalability. For example, in the original implementation, after the query results were on the client, any type of sorting meant another trip to the server. XML would let us store the XML document in a data island on the page and, with the help of XSLT, do all the sorting on the client, easing the load on the server. (For more information about XML data islands, see the two links at the end of this column.)

In our XML implementation, we decided not to include any SQL on any of the pages. Instead, we chose to use stored procedures because of the performance gains—such as faster execution. Second, because the customer was running Windows NT and SQL Server 7.0, we resisted the urge to try out SQL Server 2000's XML capabilities. Our approach was to call a function that converted the recordset that the query returned into an XML stream, as the following code illustrates:

Public Function GetXMLStreamFromADORS(ByVal objADORS, DocData)Dim oStream As ADODB.StreamSet oStream = Server.CreateObject("ADODB.Stream")objADORS.Save oStream, adPersistXML GetXMLStreamFromADORS = oStream.ReadTextSet oStream = Nothing End Function

Just as we were putting the finishing touches on our implementation, we learned that the customer was moving to Windows 2000 and SQL Server 2000. This meant that we could take of advantage of SQL Sever 2000's XML capabilities and return the results as an XML document. The cool thing is that all we had to do was add one line of code to all of our stored procedures and make slight modifications to the actual function that called them, as the following code illustrates:

CREATE Procedure usp_get_revenueASSELECT Convert(varchar(20),[User].paid_date,101) as PaidDate,CAST(SUM([Order].user_amount_paid) AS money) AS Amount_Paid,SUM([Order].user_amount_paid /   . . .  HAVING(SUM([Order].user_amount_paid) > 0) AND([User].paid_date BETWEEN DATEADD(year, - 1, GETDATE()) AND GETDATE()) FOR XML RAWGO

At first glance, the code segment above looks like a regular stored procedure—because it is. The only difference is how the data will be returned. Note the FOR XML RAW clause in the SELECT statement. Using FOR XML RAW generates generic row elements with column values as attributes. If you were to response.write the results in the browser, you would see the following results:

You can choose from three different output modes to shape your XML results: RAW, AUTO, or EXPLICIT. We chose RAW because of its generic output and simplicity. AUTO mode returns query results as nested XML elements. EXPLICIT mode is useful if the query must be written in a specific way. Which mode you choose depends on how you want or need your document to look.

For more information about XML data islands, see the following articles:
Internet Explorer XML Data Islands
Data Islands on IE 4.0