Using 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. In this article we will discuss about how to use SQLXML to
retrieve data directly from SQL server database in 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.
About the Author
Want to stay current with the latest technology
developments realted to XML. Visit
Learn XML to get your FREE subscription now!
