 |
Further
Reading ...
|
|
Web Blogs, Defined, Explained and Understood
What are Blogs? Having heard the term previously but not having paid much attention most are just to afraid to show their ignorance and ask the question, what is a blog? Lets face it; the term blog does not conjure pleasant images. Blogs...
Three Things You Can Do to Keep Your Computer Running at Maximum Performance
Although there are many things that can affect the performance of your computer, there are a few simply things you can do each month to help keep your computer running at maximum performance. This article will focus on two problems that impact the...
Why you should be using RSS
RSS opens up a whole new channel of communication between you and the browsing public. If at the moment you do not have an RSS feed then there is a whole community of people who do not know about you. Isn’t it time you started to publish an RSS...
New Search Engine
I was searching the net the other day and came across a new search engine called Foovy . It appears to be very young, but also kind of neat in that the results are pretty clean. I have not found a lot fo spam, and they allow you to submit websites...
|
|
|
How to use SQLXML to retrieve data from SQL server database?
|
 |
Written By:
Balaji B
|
|
|
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:
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 - continued below ...
|
|
|
continued ...
EmpName, EmpCity from EMP for XML RAW
The above code returns a result like,
|
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.
Want to stay current with the latest technology developments realted to XML. Visit Free XML Training to get your FREE subscription now!
** Attention Webmasters / Website Owners ** You can reprint this article on your website as long as you do not modify any of the content, and include our resource box as listed above with all links intact and hyperlinked properly.
|
|
|
|
 |
|
|
| _Additional Resources ... |
|
How disabled users access the Internet
In 1995 a new era of accessibility for disabled people began. The Disability Discrimination Act was passed, stating that: “It is unlawful for a service provider to discriminate against a disabled person by refusing to provide any service which it...
Explore the Internet in a Whole New Way
For a long time now Microsoft's Internet Explorer has ruled as 'King of Internet browsers'. Like many of Microsoft's products an initially brutal marketing campaign pushed Internet Explorer into the mainstream's consciousness and from then on it was...
ASE-PERM Regulations Published
The Department of Labor (DOL) recently published the regulations for its Program Electronic Review Management (PERM) program after two years. The regulations will take effect on March 28, 2005.
February 16, 2005- The Department of Labor (DOL)...
|
|
|
|
|
|
|
|
|
|
