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. For a more detailed analysis you can reach us at topwritingservice.com.
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.
A simple example of using the above syntax is given below:
SELECT TOP 3 *
The query produces this result:
<Employees Name="John Peter"/>
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.
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;";
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.
“Amazon and the Amazon logo are trademarks of Amazon.com, Inc. or its affiliates.”Copyright - © 2004 - 2019 - All Rights Reserved.