Retrieving Data from SQL Server Database in XML Format Quickly & Easily?
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
SELECT ... FOR XML mode
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]
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.
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.
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.
Copyright - © 2004 - 2017 - All Rights Reserved.