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.

---

Tips For Getting Technical Support Help Online

Sometimes your PC will start acting strange for no apparent reason or a program which you use regularly will all of a sudden stop working as it should. This can be a frustrating... Read More

USB Drives - What to Look For in a USB Device

Those small USB drives have so many names, pocket drives, thumb drives, USB Pen, Jump drives, and several other names, but when it comes down to it, we are all talking about a... Read More

Things You Can Do To Speed Up Your Computer

Upgrading your processor will always speed up your computer, but sometimes this will not be the best thing to do. The first thing you need to do is find out where the bottle... Read More

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

So you've got some spyware, ad-ware, or viruses on your system. For whatever reason, you are unable to get ahold of a virus detection & removal program, or a good ad-ware scanner. Maybe... Read More

How Does a Palm Pilot Work?

Just The Facts, Ma'mPalm pilots are all the rage now. Everyone has a pda or wants to get one in the near future. If you don't know much about a pda, however, it... Read More

Classification of Computers

Computers are available in different shapes, sizes and weights, due to these different shapes and sizes they perform different sorts of jobs from one another.They can also be classified in different ways. All... Read More

Tips for Buying a PC

Buying Your PCBuying a PC that's right for you and your family is not all that simple task. More so if you're going to buy an unbranded or an assembled one. But branded... Read More

Computer Performance Tips

Introduction In this article I will clearly spell out the most effective steps you can take in order to increase the performance of your computer and enhance your computing experience.More Memory (RAM) If... Read More

5 Steps to Remove Spyware for Free

Have you ever tried to remove Spyware off your PC only to find out it never left or it came back? Tired of all those annoying Popups, Viruses, and Ads on your system... Read More

Dont Let the Internet Overstimulate Your Mind

The Internet is an awesome tool, but be careful and aware that the cloud of over stimulation doesn't invade your mind.It seems we have to become aware of a new problem that is... Read More

Your Computer Cant Keep Time

A computer needs a certain amount of information to operate; for example, the date and time, the amount of memory installed, the number of drives and their configuration, and so on. In the... Read More

Apache, MySQL & PHP for Windows

Apache, MysQL and PHP for Windows could be a nice nice thing to have on your Windows workstation. You could try and experiment with all kinds of nice PHP and MySQL based applications... Read More

Put Order and Information into File Names

Unless you place your faith in internal search engine software or document databases to track your work, consider a relatively low-tech enhancement to your work methods. I promise it will pay big dividends.Consider... Read More

Uninstall Windows Media Player 10 and Start Enjoying Your Media Again

Windows Media Player 10 has been full of bugs from Microsoft since they offered it as a download from their site. Windows Media Player 9 was nice and simple. Windows Media Player 10... Read More

iPod - Learn How It Can Solve Everyday Problems

All over the world, people carry with them walkmans and other music devices so that they can play their favorite music as they move around from place to place. The latest thing in... Read More

MCSE 70-290 Certification Primer

Microsoft Certifications are one of the most widely acclaimed, pursued, and achieved technical certifications in the IT industry. The demand for Microsoft Certified Professionals in the job market is increasing every year.Microsoft offers... Read More

Computer Viruses are Bad Luck

OK, so you caught a computer virus and your system is all screwed up and you're frustrated and angry and upset. Bad luck.Here's something that might make you feel better ...The universe has... Read More

How to Save an Image in a SQL Server Database?

Most of the web applications have a lot of images used in it. These images are usually stored in a web server folder and they are accessed by giving the relative path to... Read More

Tips For Finding Great Deals On Computer Accessories & Supplies

Tip #1. Do a Google search. Don't be to general in your search, type in exactly what your looking for. Try putting the words you type in quotes to narrow the search.Tip #2.... Read More

Basic Computer Maintenance

One of the most common questions computer users ask is, "How do I maintain my computer and keep it running great?" A computer is a lot like a car?it costs more than you... Read More

Compile .BAT Files into Native Windows Applications (.EXE)

Since the DOS days, batch files have been one of the most convenient and immediate ways to accomplish certain tasks. Even today it's possible to find some remarkably sophisticated utilities written as command-line... Read More

Computer Geeks and Garden Gnomes

First and foremost before I begin my ranting it is worth mentioning that there are many very helpful and considerate people out there in the cyber wilderness who devote much of their time... Read More

Web Standards

HTTP ProtocolThe web is run on port 80. You are probably wondering what "port 80" is, right (whether you actually are or not is irrelevant)? Well, the answer is easy (not really). See,... Read More

A Beginners Guide to Avoiding Viruses

"Aaaaaahhhhhh! I've been invaded by a virus!" Getting a virus means getting sick and no one in their right mind wants to be ill. Well, now that computers have become our close friends,... Read More

Computer Consulting 101 PC Troubleshooting Advice

While most small businesses really do need to find a good local computer consulting business to take care of their computer problems, there are some computer problems that are simple enough for even... Read More

The Ipod Mini: Why Its Not that Hot

I got my eyes set on a iPod mini, as it looked good for quite some reasons: it's small enough to put inside a pant's pocket, it has a lot of accessories to... Read More

Selecting the Perfect Big Screen TV

Selecting a TV isn't as easy as it used to be in years gone by. Rather than going to the local department store and choosing a console that would take up a large... Read More

D2X Digital SLRCoolpix 8800 Actually Refers to Two Nikon Cameras

Addressing a D2X Digital SLRCoolpix 8800 search, this article provides a quick comparison of the Nikon D2X digital SLR and Coolpix 8800 cameras.Nikon D2X Professional Digital SLRCheck out a Nikon D2X review or... Read More

Why Using an Ad Blocker Can be a Bad Idea

Ad Blocking is getting to be a common sport on the internet. Now there are some legitimate uses for an ad blocker. Some ad companies resort to downright harassment when serving their ads... Read More