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.
Want to stay current with the latest technology developments realted to XML. Visit Free XML Tutorial 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.
![]() |
|
![]() |
|
![]() |
|
![]() |
When you think of a help desk, what do you... Read More
Your Bible At Your FingertipsIt is fairly easy to find... Read More
Peoples' private information needs to stay private, even after it... Read More
Up until the recent past, those who wanted to take... Read More
Plenty! When we made the decision to be our own... Read More
MMC and SDFlash memory is available in so many formats... Read More
So I'll start from the very beginning.One day I was... Read More
When studying for your Cisco CCNA, CCNP, or CCIE exam,... Read More
GOT VIRUS? Your Data is NOT lost forever!In the wake... Read More
The DNS (Domain Name System) servers are what your computer... Read More
How long do you think DVDs have around? 20 years?... Read More
Ad Blocking is getting to be a common sport on... Read More
The following practice, if done regularly, may help you to... Read More
In the 1950's and 1960's Polaroid's instant cameras were all... Read More
With the new technology used to transfer information to from... Read More
I met an entrepreneur who hole heartedly disagree with an... Read More
CCNA FAQQ. What exams do I have to take to... Read More
There are plenty of articles out there about how to... Read More
Addressing a D2X Digital SLRCoolpix 8800 search, this article provides... Read More
So you've got some spyware, ad-ware, or viruses on your... Read More
Most people download and save so much information on their... Read More
On 21 August 2003 Symantec Security Response upgraded the W32.SOBIG.F... Read More
It's free, it comes with Windows and it's used by... Read More
I've worked my way from the CCNA to the CCIE,... Read More
It's hard enough as it is these days to get... Read More
If you're like me, you occasionally find the ubiquitous mouse... Read More
Yes, it's true. You may have inadvertently invited a spy... Read More
IntroductionMac OS X is the most technologically advanced operating system... Read More
The Internet is an awesome tool, but be careful and... Read More
Feeling overwhelmed in selecting a new TV? With all the... Read More
Here are some tips on how to use screensavers:First of... Read More
MP3 players are everywhere! It seems that the number of... Read More
Your first step in removing dangerous infections from your computer... Read More
System File Checker is a great utility that is typically... Read More
Passwords have a strange dual nature. The stronger and safer... Read More
You have your television and home theater receiver; you just... Read More
In Windows Xp, you can install two operating systems on... Read More
Your Bible At Your FingertipsIt is fairly easy to find... Read More
Webmasters usually sit at their computers much longer in a... Read More
The following practice, if done regularly, may help you to... Read More
JVC developed and used a high-performance reflective film to produce... Read More
Computers are available in different shapes, sizes and weights, due... Read More
With the bewildering number of digital cameras on the market,... Read More
So you've got some spyware, ad-ware, or viruses on your... Read More
If you don't have an mp3 player, and even if... Read More
It's hard enough as it is these days to get... Read More
Most people download and save so much information on their... Read More
The best way to get the gaming computer that you... Read More
Computers are everywhere, and vary in specification, brands, sizes, shaped,... Read More
IF WE say that it was mainly because of the... Read More
Are you frustrated with your PC?Is it feeling sluggish or... Read More
Computer related terminology could sometimes be daunting to newcomers. These... Read More
Upgrading your processor will always speed up your computer, but... Read More
If you've been kicking around the idea of building your... Read More
Freezing is also known as crashing or hanging. It's frustrating.... Read More
Those small USB drives have so many names, pocket drives,... Read More
HTTP ProtocolThe web is run on port 80. You are... Read More
One of the most confusing parts of beginning your Cisco... Read More
I'm eighty-one years old and I bought my first computer... Read More
If you utilize a computer at home or work it... Read More
If you use a computer, you need to know more... Read More
With their solid-state technology, compact size, and abundant memory, portable... Read More
Tip #1. Do a Google search. Don't be to general... Read More
Plasma and LCD TVs are the latest trend in home... Read More
Yes, it's true. You may have inadvertently invited a spy... Read More
There have been some questions on anti-skip protection on mp3... Read More
Personal Technology |