How to Retrieve Data from SQL Server Database in XML Format?

After 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.

Stay current with the latest technology developments related to XML and other related technologies? Visit Understanding eXtensible Rights Markup Language - XrML to get your FREE subscription now!

Stacks Of Data, And Nowhere To Store Them?
Ever tried looking for a particular paper in a stack of hundreds of papers two minutes before a meeting that might change your career?A database would allow you to store your data and then retrieve it at anytime, just by entering a particular piece of information that identifies the data you require.While I was complaining to my friend about how I was not only late for that meeting but also could not get the information that I needed, because I c...

Novell Operating System - Recovery of Compressed Data
Problem DescriptionData stored in a working Novell server is automatically compressed to save space, and is obviously decompressed before it is displayed to a user. Phoenix Novell recovers lost data exactly as it was stored in the hard disk, and the recovered data may be compressed. This gives rise to the problem of recovered data being unusable (because it is in a compressed format).Phoenix, however, has a new and advanced uncompression engine t...

Basic Concepts for a Data Warehouse
The concept of a data warehouse is to have a centralized database that is used to capture information from different parts of the business process. The definition of a data warehouse can be determined by the collection of data and how it is used by the company and individuals that it supports. Data warehousing is the method used by businesses where they can create and maintain information for a company wide view of the data.Data Warehouses becam...

Recovering Lost Data
Are you worried that you may have accidentally deleted some important data, or perhaps you have experienced a mechanical or problem, or software or files have corrupted or failed? You may be concerned that you wont be able to retrieve those files, but luckily there is an answer for you, stop what you are doing and call in the data recovery experts.You dont have anything to lose if you think you may have already lost some vital data. Data recovery...

OST File Recovery and PST File Recovery
Email communication has expanded itself from professionals to normal home users. As a child enters into teenage, he becomes familiar with the power and usefulness of email communication and uses emails to correspond within his circle of friends and relatives. If we quickly look at some of the public email portals like Yahoo, Hotmail, Rediff etc; it can be analyzed that these portals regularly increase email storage limits for its users and as lo...