How
to use SQLXML to retrieve data from SQL server database?
With
SQL Server 2000 and above versions you can retrieve data from SQL Server
in XML format directly from the database. Many XML related features were
incorporated in the SQL Server database. This enables the development
of XML enabled applications very easy and it makes exchange of data over
internet easy. Although it is not possible discuss all of the features
here we will look at some features that enables you to retrieve data from
the SQL Server in the XML format.
_______________________________________________
_______________________________________________
There are some SQL commands and clauses that help you to retrieve data
from the SQL Server in the XML format. Syntax for such is given below:
SELECT ...
FOR XML mode [, XMLDATA] [, ELEMENTS] [, BINARY BASE64]
There are
different modes and that take the values of
·
AUTO – In this mode every row of the result of the query forms an
element in the returned XML document.
· EXPLICIT – This mode defines how to map the columns of the
results returned by the query to the XML elements and the attributes.
· RAW - All the fields of the result are identified as attributes
of an element.
· NESTED – The result of this mode is the same as the AUTO
mode but the formatting has to be done at the client side.
A simple
example of using the above syntax is given below:
SELECT TOP
3 *
FROM (SELECT FirstName+' '+LastName as Name FROM Employees)
FOR XML AUTO
The query
produces this result:
<Employees
Name="John Peter"/>
<Employees Name="Andrew Dixon"/>
<Employees Name="Tom Hanks"/>
There are
optional parameters in the ‘FOR XML mode’ clause. The XMLDATA
option adds an XSD schema which describes the XML format of the result
returned. The ELEMENTS options make the fields of the tables in the database
to be returned as child elements. If the ELEMENTS option is not there
the fields of the tables are returned as attributes instead of child elements
of the XML document. The ELEMENTS option is allowed only in the AUTO mode.
The BINARY BASE64 option is used to retrieve any binary data. The returned
binary data is returned in BASE64 encoded format. The binary data can
be retrieved in the RAW or EXPLICIT mode.
An example
of the RAW mode is given below:
SELECT EmpName,
EmpCity from EMP for XML RAW
The above
code returns a result like,
<row EmpName=”John”
EmpCity=”New York”/>
In the above
result you can note that the fields of the resultset are returned as attributes
of the element.
.Net provides
with managed classes for interacting with SQL Server. The managed classes
of SQLXML provided by .Net are:
·
SqlXmlCommand – This is used to perform queries that are stored in
XML documents which are also called XML templates. This class also provides
support for client side XML processing.
· SqlXmlParameter – this is used to provide parameterized
values and is used along with SqlXmlCommand object.
· SqlXmlAdapter – this is the adapter class for SQLXML provider.
This is used to fill the dataset.
· SqlXmlException – this is used to trap the error information
from the SQL Server.
A sample
code using the managed classes is given below. For detailed information
on the usage of the above managed classes you can refer to the MSDN documentation.
static string
cstr = "Provider=SQLOLEDB;Server=(local);database=EmpPersonal;";
public static void testingSqlXml ()
{
Stream oStr = Console.OpenStandardOutput();
SqlXmlCommand sqlcmd = new SqlXmlCommand(cstr);
sqlcmd.Root = "Employees";
sqlcmd.CommandType = SqlXmlCommandType.Sql;
sqlcmd.CommandText = "SELECT EmpName, EmpCity FROM Employees FOR
XML AUTO";
strm = sqlcmd.ExecuteToStream(oStr);
oStr.Close();
}
Thus we have
seen that SQLXML can be used to retrieve data directly from the SQL Server
in XML format. It is easy to handle and transmit data through the internet
if it is in the XML format and this can be easily achieved using the SQLXML.
_______________________________________________
_______________________________________________
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
|
|