Retrieving Data from SQL Server Database in XML Format Quickly & Easily?
From the release of the SQL Server 2000 the way the data is retrieved has taken a new dimension. The support for XML format is there from the version of SQL Server 2000.
SELECT ... FOR XML mode
The values of the ‘mode’ may be ‘auto’, ‘explicit’, ‘raw’, and ‘nested’. Depending on the mode that you are using the format of the XML output varies. If the mode is ‘auto’, the resulting XML document will have an element for each row that is found in the table of the SQL Server database.
Apart from these there are optional parameters for the SQL query. The optional parameters as the name indicates can either be used or neglected. The optional parameters that can be used are Binary Base64, Elements, and XMLData. With the optional parameters in place the syntax would take the form,
SELECT ... FOR XML mode [, BINARY BASE64] [, ELEMENTS] [, XMLDATA]
The Binary Base64 option is used if you want to retrieve data in the binary format from the database. Binary data that is found in the database should be retrieved using this option. The modes that are used to retrieve data in binary format are the ‘raw’ and the ‘explicit’ modes.
Managed classes are available in the .Net framework for interacting with the database. The Managed classes that are useful for this are SqlXmlCommand, SqlXmlParameter, SqlXmlAdapter, and SqlXmlException.
The SqlXmlCommand is used to query the XML templates. These are the XML documents that have the SQLXML queries in them. Processing the queries on the client side is possible with this managed class.
A managed class called the SqlXmlAdapter class is available which can be used to fill the dataset. This is the adapter class for the provider.
Another managed class called the SqlXmlParameter is available which can be used to pass parameters. This class is used along with the SqlXmlCommand managed class.
A class for trapping errors from the SQL Server is available which is called the SqlXmlException managed class.
A simple example of using the managed classes for getting XML output from the SQL Server database is given below. This code uses the XML AUTO mode for generating the XML data.
static string xmlstr = "Provider=SQLOLEDB;Server=(local);database=Orders;";
For more information and samples on the managed classes that are used for retrieving data in XML format from SQL Server you can refer to the MSDN documentation.
Since we know that the results of the query are in the form of XML, it is possible to write your own ASP or ASP.Net code to display the values retrieved in XML format in the webpage. You can use the XSL for formatting the output in the way you want in the webpage. This allows the user to see the values in a particular format using the browser of their choice.