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.
_______________________________________________
_______________________________________________
FREE
Subscription
Subscribe to our mailing list and receive new articles
through email. Keep yourself updated with latest
developments in the industry.
Note
: We never rent, trade, or sell my email lists to
anyone.
We assure that your privacy is respected
and protected.
_______________________________________
Recommended
XML Books
|
|