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.

---

The Importance Of Email Backup

Viruses, software failures, power failures, human errors, hard drive failures... Read More

The Many Benefits of Owning a DVR

Has This Ever Happened To You? OK.. it's Friday night,... Read More

Flash Memory, Part I: MMC and SD

MMC and SDFlash memory is available in so many formats... Read More

Simple Overview Of Computer

Computer is an electronic machine work on the instructions of... Read More

A Beginners Guide to Avoiding Viruses

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

Basic Computer Thermodynamics

That desk in front of you and everything else around... Read More

Best PC Pocket GPS

Stop Getting LostOne of the greatest uses for a pocket... Read More

Learn the Lost Art of Touch Typing

Computers have replaced typewriters, but two-finger, hunt-and-peck typing can never... Read More

How To Have Two (Multiple) Copies Of Windows

Having two operating systems is not as difficult as many... Read More

Make Windows XP Run Faster!

A friend told me: "My computer startup seems to be... Read More

Improve PC Performance - 6 Tips You Must Know

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

Favorite Things To Do With Home Technology

Save money. Save 10-15% on utility bills per month. Work... 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

Five Simple Steps to Speed up Your Computer

Most people download and save so much information on their... Read More

PC Tools Youd Never Think You Need

Do you use Windows standard uninstall feature? How do you... Read More

Digital Cameras: Hot Gear with the Coolest Features!

Cameras: still known for taking pictures but assumed as digital... Read More

COOKIES - What Are They!!

Cookies, not the kind that Mom makes, but the computer... Read More

Three Things You Can Do to Keep Your Computer Running at Maximum Performance

Although there are many things that can affect the performance... Read More

Cisco CCNA Certification: Five Things To Do DURING The Exam

There are plenty of articles out there about how to... Read More

Selecting the Perfect Big Screen TV

Selecting a TV isn't as easy as it used to... Read More

Upgrading Your PC for Non-experts

IntroOne of the big advantages of PCs over earlier types... Read More

Quick System Restore with ASR Backups

ASR (Automated System Recovery) is a feature available on the... Read More

5 Minute Guide to Video Editing for Beginners

Getting started with video editing is very simple you only... Read More

Selecting a Personal Digital Assistant

A Computer in Your HandCarrying around an address book and... Read More

Apache, MySQL & PHP for Windows

Apache, MysQL and PHP for Windows could be a nice... Read More

What Exactly are Screensavers? - part II

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

Virtual Memory - What is It?

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

How to Switch to Firefox and Why You should

First things first, what is Firefox? Well, it's a browser.... 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

Is DVD Storage An Attractive Alternative For Your Computer Backup?

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

Lightning Season: How to Avoid Data Loss

We have just entered the time of year that most... Read More

How To Become A True CCNA

I've worked my way from the CCNA to the CCIE,... Read More

5 Steps to Remove Spyware for Free

Have you ever tried to remove Spyware off your PC... Read More