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.

---

Get Ahead When You Build Your Own Computer

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

Computer Performance Tips

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

Tips For Finding Great Deals On Computer Accessories & Supplies

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

D2X Digital SLRCoolpix 8800 Actually Refers to Two Nikon Cameras

Addressing a D2X Digital SLRCoolpix 8800 search, this article provides... Read More

Bluetooth Headphones For Your PDA

Nothing is worse than having to negotiate all kinds of... Read More

A Peek Into the Near Future of Electronics Technology

How long do you think DVDs have around? 20 years?... Read More

How Does My PC Get Hot

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

How To Speed Up Your Aging PC And Make It Healthier Too

With a little tweaking and with proper tools, you can... 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

Occupational Therapy and PDAs

Occupational Therapy Made EasierMedical downloads for the pda have improved... Read More

Cisco CCNA Certification: Five Things To Do DURING The Exam

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

How to Place Home Theater Speakers

You have your television and home theater receiver; you just... Read More

Cisco CCNA / CCNP Certification: OSPF ASBRs Explained And Illustrated

When I first started studying for my CCNP, some of... Read More

Tips for Buying a PC

Buying Your PCBuying a PC that's right for you and... Read More

Setting File Permissions On A Unix Server

File permissions are one of the strengths that the Unix... Read More

Home Video? Bring it On

So you got yourself a digital camcorder. If you want... Read More

Enhanced Web Browsing With Toolbars

As the Web grows more crowded and just plain "noisy"... Read More

Cisco Certification: Building Your Own Home Lab, Part I

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

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

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

Is Desktop Search Deserving?

Google, in their march to stay ahead of the pack... Read More

Quick System Restore with ASR Backups

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

Selecting a Personal Digital Assistant

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

Some Thoughts on Choosing a Flash MP3 Player

Flash mp3 players come with an exciting array of features,... Read More

Is Your Web Browser Putting You At Risk?

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

Uninstall Windows Media Player 10 and Start Enjoying Your Media Again

Windows Media Player 10 has been full of bugs from... Read More

Reliable File and Folder Sharing in Windows Xp

This tip is on sharing files and folders on a... Read More

Gain Control Over Your Screensavers With These Simple Steps

We all enjoy our favorite screensavers but in the same... Read More

Getting Started with HTML

The basic language of the internet is html. If you... Read More

Bluetooth Technology: Tips for Buying Headsets or Headphones

The technological horizon has always got something new to offer,... Read More

Why There Are Color Differences in Printing from Your PC

This article describes the basic properties of color, what the... Read More

Tips For Getting Technical Support Help Online

Sometimes your PC will start acting strange for no apparent... Read More

Does Microsoft Show Hackers How To Attack?

After another security hole recently surfaced in Microsoft's Windows operating... Read More

DVD The Ultimate Consumer Digital Media

Digital Video Disc or as it is sometimes referred to... Read More