How to Use SQLXML to Retrieve Data from SQL Server Database?

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.

---

Help, I Need a New HDTV! (Part 2 of 5)

Feeling overwhelmed in selecting a new TV? With all the... Read More

Do Higher Digital Camera Prices Mean Better Cameras?

Understanding digital camera prices makes finding the best camera value... Read More

How to Use SQLXML to Retrieve Data from SQL Server Database?

Using SQL Server 2000 and above versions you can retrieve... Read More

The Benefits of the New Firefox Browser

You probably heard of the new Firefox browser version 1.0... Read More

Can Movie Theatres Compete with Home HD TV in the Future?

The battle is heating up for market share, home movie... Read More

How To Increase Computer Speed And Performance - Great Tips & Tricks

Many computer users are worried about their computer's performance. After... Read More

Connect Your IPAQ to Linux

Choose Not To ConformPalm pilots and pdas are constantly growing... Read More

Image Formats: GIF, JPEG, BMP

When browsing the internet you are likely to come across... Read More

Temporary Fixes to Spyware, Ad-ware, and Virus Infections

So you've got some spyware, ad-ware, or viruses on your... Read More

How Do MP3 Players Work? Digital Audio Technology Revolutionizes Music Enjoyment

Millions have enjoyed recorded music since 1877 when Thomas Edison... Read More

How to Buy a Plasma Television Set

Most people think that all you have to do to... Read More

Virtual Memory - What is It?

I recently got an e-mail asking about virtual memory. The... Read More

10 Tips to SPEED Up Your PC

1. Let your PC boot up completely before opening any... Read More

Toshiba Wins Hollywood Support for the HD DVD format

What led them to this choice, as all four studios... Read More

How To Remove One Operating System from a Dual Boot Windows XP Computer

In Windows Xp, you can install two operating systems on... Read More

Passwords and the Human Factor

Passwords have a strange dual nature. The stronger and safer... Read More

Troubleshooting Computer Freeze (Lockup) Problems

How familiar does the following sound? Your computer was working... Read More

The Ipod Mini: Why Its Not that Hot

I got my eyes set on a iPod mini, as... Read More

How to Protect your PC from Spyware in the Cyber Age

Wouldn't you be shocked to find that your personal sensitive... Read More

5 Ways to Speed Up Your PC

No matter how fast your processor and regardless of how... Read More

Cisco Certification: Building Your Own Home Lab, Part I

CCNAs and CCNA candidates hear it all the time: â??Get... Read More

What Has A Portable MP3 Player Have To Do With Your Business

Plenty! When we made the decision to be our own... Read More

Top 9 Ways To Speed Up Your Surfing & Computing

It seems like fast never quite rates fast enough.No matter... Read More

Dynamite Comes in Small Packages - Tiny Personal Audio MP3 Players Pack Powerful Music Enjoyment

MP3 players are Hot! Playing music has come a long... Read More

Emulation Manual - A Complete Guide on How to Change Your Windows XP to Mac OS X

IntroductionMac OS X is the most technologically advanced operating system... Read More

Get Ahead When You Build Your Own Computer

If you've been kicking around the idea of building your... Read More

Improve PC Performance - 6 Tips You Must Know

Are you frustrated with your PC?Is it feeling sluggish or... Read More

Consumer Electronic Information: The Basics of the DLP Projector

Projectors have come a long way in the past few... Read More

Customize Your Portable Player with an MP3 Player Accessory

Everywhere you look today people are listening to personal audio... Read More

4 Easy Ways to Speed Up A Sluggish PC

Computers are supposed to speed up our productivity?to help us... Read More

Buying A PC Flat Screen Monitor

For six years, my Samsung PC 13.8 inch SyncMaster conventional... Read More

Cisco Certification: Building Your Home Lab, Part II

In the first part of this home lab tutorial, CCNA... Read More

What Exactly are Screensavers? - part II

Here are some tips on how to use screensavers:First of... Read More