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.

---

Is DVD Storage An Attractive Alternative For Your Computer Backup?

If you have a computer for home use or for... Read More

PDA Bible Free Download

Your Bible At Your FingertipsIt is fairly easy to find... 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

Repairing A Corrupt .RAR/.ZIP Archive

Step 1:Open WinRAR and browse to the folder with your... Read More

This Page Cannot Be Displayed ? What to Do When Your Internet Breaks

The DNS (Domain Name System) servers are what your computer... Read More

The Newbies Guide to Personal Computer Maintenance

When you turn on your computer, does it act like... Read More

Review of Rio MP3 Players

Below you will find some useful information and comments about... Read More

Basic Computer Maintenance

One of the most common questions computer users ask is,... Read More

Learning To Navigate Ciscos Online Documentation

When studying for your Cisco CCNA, CCNP, or CCIE exam,... Read More

Your Computer Cant Keep Time

A computer needs a certain amount of information to operate;... Read More

Computer Geeks and Garden Gnomes

First and foremost before I begin my ranting it is... Read More

Lives With Games Tradition and Industry

The United States Of America citizen feels that games is... Read More

Nephrology and Dialysis For a PDA

Saving Lives With A Pocket PCFree medical downloads can be... Read More

5 Steps to Removing PC Clutter

It's hard enough as it is these days to get... Read More

How To Search Your PC With Google Desktop

In this era of Internet, most people are frequent users... Read More

Digital Cameras + Photo Printers = Quality Instant Photographs

In the 1950's and 1960's Polaroid's instant cameras were all... Read More

Font Organizers Review, Part I

Do you know how many fonts are currently installed on... Read More

How Does My PC Get Hot

There are many sources of heat that can raise the... Read More

404 Error Pages: What Are They And How Do You Create One?

We've all seen them, you've been browsing a website and... Read More

Wordpress Plugins

Maybe you always wanted a feature that hasn't been available... Read More

What Are You Looking For In A Cheap MP3 Player?

Are you stymied by the vast offerings in cheap mp3... Read More

Tips For Finding Great Deals On Computer Accessories & Supplies

Tip #1. Do a Google search. Don't be to general... Read More

Is Your Web Browser Putting You At Risk?

It's free, it comes with Windows and it's used by... Read More

Cisco Certification: The Most Important Cisco Study Youll Ever Do

All of us are familiar with the pyramids of Egypt.... Read More

5 Ways to Speed Up Your PC

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

How To Buy An MP3 Player

Are you looking for an MP3 player but am not... Read More

JVCs First Three-Layer Combo-Disc Blu-Ray / DVD

JVC developed and used a high-performance reflective film to produce... Read More

What Exactly are Screensavers? - part I

After reading this good article you will know some important... Read More

A Beginners Guide to Avoiding Viruses

"Aaaaaahhhhhh! I've been invaded by a virus!" Getting a virus... Read More

USB Drives - What to Look For in a USB Device

Those small USB drives have so many names, pocket drives,... Read More

5 Simple Ways To Increase Your Computer Speed & Help Your Computer Run More Efficiently!

If you're a computer expert, you'll probably already know about... Read More

Computer Performance Tips

Introduction In this article I will clearly spell out the... Read More

The Advantages of Portable MP3 Players

With their solid-state technology, compact size, and abundant memory, portable... Read More