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.

kitchen deep cleaning Deerfield ..
In The News:

Airport workers report surge in fake lost luggage claims as scammers retrieve discarded baggage tags containing passenger names and travel information.
Unlike robotaxi competitors, Tensor focuses on consumer-owned self-driving cars that adapt to highways and urban roads with full redundancy systems for safety.
Medicare scams cost $54 billion in 2024 as fraudsters target beneficiaries with fake calls demanding payments and personal information to steal benefits.
Scammers create fake Evite invitations that mimic legitimate event emails, requiring users to verify senders and use antivirus software for protection.
The new Apple Watch hypertension feature passively monitors blood pressure patterns over 30 days using sensors to detect chronic high blood pressure signs.
Chrome extension spyware disguised as a free VPN service highlights security risks after it captured private browsing data from trusted sites.
New research shows how fatty acids in cooking oil can safely dissolve and recover silver from circuit boards without harmful chemicals or environmental damage.
The Fox News AI newsletter gives you information on the latest AI technology advancements, and about the challenges and opportunities AI presents now and for the future.
Anthropic investigates alarming AI abuse case where hacker automated entire cybercrime campaign using Claude, stealing sensitive data from defense and healthcare firms.
TikTok, Meta and YouTube restrict Charlie Kirk shooting videos with age gates and warnings while X faces criticism for allowing continued circulation.
Cybercriminals use fake troubleshooting websites to trick Mac users into running terminal commands that install Shamos malware through ClickFix tactics.
San Francisco startup Fable launches Showrunner, an AI platform dubbed the 'Netflix of AI' that generates animated episodes from text descriptions with Amazon support.
Apple raised iPhone prices for some models despite receiving tariff relief from President Donald Trump, with the new lineup starting at $799 for the base model.
A two-story 3D concrete printed home in Western Australia demonstrates faster construction methods that could reshape American housing amid rising costs.
Credit scores remain important during retirement for insurance rates and housing applications, while seniors become prime targets for identity theft and financial scams.
Scammers now send unexpected packages with QR codes that redirect victims to fraudulent websites or download malicious software to steal sensitive information.
Meeting AI tools record private conversations alongside work discussions, creating privacy risks that can be managed with proper settings and awareness.
Hotel privacy concerns are valid but rare, with methods to detect hidden tech using smartphone flashlights, mirror tests and scanning apps.
Improve your Wi-Fi speed and reliability with 10 simple router optimization tips that don't require special apps or expensive subscriptions.
A Columbia University breach exposed names, Social Security numbers and academic records of nearly 869,000 people, with notifications beginning in August.
Rental car drivers use AI-powered apps like Proofr to protect themselves from unfair damage fees as major companies deploy automated inspection tools.
Fox News' AI newsletter brings you the latest on technology advancements around artificial intelligence.
OnTrac data breach between April 13-15, 2025, exposed personal information of over 40,000 people including Social Security numbers and medical records.
A woman named Wika announces her engagement to an AI chatbot sparking worldwide debate about virtual relationships and technology.
The notorious people search site National Public Data relaunches despite a previous breach affecting 3 billion individuals, raising fresh privacy concerns.

How To Recognize The Telltale Signs Of Spyware

Spyware is the software that collects information about your online... Read More

Image Conversion In Computers

JPEG, GIFF/JIFF, BMP, and TIFF are the most commonly used... 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

Looking For an MP3 Player?

If you don't have an mp3 player, and even if... Read More

iPod - Learn How It Can Solve Everyday Problems

All over the world, people carry with them walkmans and... Read More

Why There Are Color Differences in Printing from Your PC

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

Nephrology and Dialysis For a PDA

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

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

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

Ergonomics and Healthy Computing - Positioning Your Body For Maximum Comfort At Your Computer

Do you remember the old saw about how computers would... Read More

Your Computer Cant Keep Time

A computer needs a certain amount of information to operate;... 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

Do Higher Digital Camera Prices Mean Better Cameras?

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

The Newbies Guide to Personal Computer Maintenance

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

Selecting the Perfect Big Screen TV

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

Basic Computer Thermodynamics

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

Reliable File and Folder Sharing in Windows Xp

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

Are You Putting Off Productivity?

I recently took a good look at what was keeping... Read More

How to Place Home Theater Speakers

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

Cisco Certification: Building Your Own Home Lab, Part I

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

iPod users get the picture

iPod users start to get the picture and it's turning... Read More

Advantages and Guidelines of Automated Testing

"Automated Testing" is automating the manual testing process currently in... Read More

Maintain Your Computer ? Keep Your Business Running

Maintaining your computer is extremely important ? especially if you... Read More

How to Switch to Firefox and Why You should

First things first, what is Firefox? Well, it's a browser.... Read More

Build Your Own Computer

I'm eighty-one years old and I bought my first computer... Read More

Portable Technology: Getting to Know the Treo

Up until the recent past, those who wanted to take... Read More

interior house cleaning Highland Park ..