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.

With that it is possible to retrieve data from the SQL Server database in the XML format. SQLXML is used for this purpose. With such possibilities it is possible to access data in the SQL Server using URL based queries where the queries are transported using HTTP.

If you are already familiar with the SQL queries then it is very easy to create queries that return data in XML format. The syntax for such SQL queries would be,

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.

In the ‘explicit’ mode it is possible to define how the columns of the table should be returned to the query. In the ‘raw’ mode all the fields of the table are considered as the attributes of the element of the XML data that is returned. The columns that have null values are not included. The returned elements have a ‘row’ prefix to it.

The ‘nested’ mode allows formatting to be done at the client side and it is the same as the ‘auto’ mode except for this difference. The explicit mode is the most powerful mode for returning data. It is possible to define how you want the data and you can even use filters and sorts to get the data in the way you want.

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.

The Elements option is used to return the data in the table as child elements. The fields of the row become the attributes of the element returned if you are not using the Elements option. Thus for each row you get an element with child elements being the fields of the row.

Auto mode is the only mode in which you can use the Elements option. If you want to define the format that is returned you need an XSD schema for that. The XMLData option allows this. This option adds a schema so that you get the format that you want for your XML data.

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;";
public static void SampleSqlXml ()
{
Stream objStr = Console.OpenStandardOutput();
SqlXmlCommand sqlcmd = new SqlXmlCommand(xmlstr);
sqlcmd.Root = "Orders";
sqlcmd.CommandType = SqlXmlCommandType.Sql;
sqlcmd.CommandText = "SELECT OrderNo, OrderValue FROM Orders FOR XML AUTO";
strm = sqlcmd.ExecuteToStream(objStr);
objStr.Close();
}

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.