- XML Representation Options
- Building XML Response in a Server-Side Script
- Building the XML Response in the SQL Server
- Building the XML Response with XSLT
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Building the XML Response in the SQL Server
Building the XML response in the server-side script is undoubtedly the most flexible option, but it imposes significant resource consumption on the web server, because the scripting languages usually aren’t highly optimized (ASP.NET being somewhat of an exception). Thus, a much better solution is to perform the majority of the XML transformation work in the database server, assuming that it can return query results in XML format.
One of the database servers supporting XML-formatted results is Microsoft SQL Server 2005, which we’ll use as a sample SQL server in the rest of this section. (Another widespread choice is Oracle.)
XML formatting of query results in Microsoft SQL Server is extremely simple: Just append the FOR XML option at the end of the SELECT clause, resulting in XML-formatted query results. Additional options specify whether you want to encode columns as attributes or child nodes, and the name of the row element. (The default is row.) You could also specify the exact format of the XML results by using the EXPLICIT option, which can even transform the JOIN results into an XML hierarchical tree structure.
The SQL server usually returns an XML fragment (a string without the root element and the XML declaration). To return a valid XML document to the browser client, you have to insert the missing bits and pieces. The subroutine in Listing 9 sets all the required HTTP headers and produces a valid XML document when given XML-formatted SQL query results.
Listing 9 Outputting SQL query results as a valid XML document.
Sub OutputXMLQueryResults(RS,RootElementName) Response.Clear Response.ContentType = "text/xml" Response.Codepage = 65001 Response.Charset = "utf-8" Response.Write "<?xml version=’1.0’ ?>" Response.Write "<" & RootElementName & ">" While Not RS.EOF Response.Write RS(0).Value RS.MoveNext WEnd Response.Write "</" & RootElementName & ">" Response.End End Sub
Using the functionality of SQL Server 2005 in combination with the above subroutine, the script that displays the contents of the Categories table is reduced to a single line of code, as shown in Listing 10.
Listing 10 Displaying the Categories table via SQL Server’s XML functionality.
OutputXMLQueryResults DB.Execute("SELECT * FROM Categories FOR XML RAW(’Category’),TYPE"),"CategoryList"