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.

Wood Dale Chicago prom limo .. Lockport Chicago limo O’Hare
In The News:

Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents
Internet browsers are useful, but they can be dangerous. Clicking on malicious links can direct you to dangerous websites that steal information or infect your device.
Apple's latest iOS update introduces advanced artificial intelligence capabilities, which may be capturing and analyzing sensitive information.
Breach site confirmed 56,904,909 Hot Topic users' data leaked online. Tech expert Kurt “CyberGuy" Knutsson says the company's silence makes matters even worse.
Tech expert Kurt “CyberGuy" Knutsson discusses how an innovative Easy-Way kit turns standard strollers electric, simplifying navigation for parents.
Tech expert Kurt “CyberGuy" Knutsson talks about T-Mobile being hacked in broad cyberattack on global phone and internet companies.
Tech expert Kurt “CyberGuy" Knutsson shows you how to add, customize and manage widgets for quick access to apps and info from your iPhone home screen.
Wrong Google searches can not only compromise your device and personal data. They can also bring law enforcement to your doorstep. Hackers are targeting Google searches.
China's Shanghai Kepler Robotics has developed a new humanoid robot that can carry up to 35 pounds per hand for commercial applications across various industries
Checking your hearing has gotten easier with the new hearing test feature in AirPods Pro 2. You can take reliable hearing tests using your iPhone or iPad.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
Kurt "CyberGuy" Knutsson explains how a VPN — virtual private network — works and how running it can slow down the operation of your device.
The WalkON Suit F1, an exoskeleton developed to help people with disabilities, can actually walk over to a person in a wheelchair, solving a major problem.
ClickFix, a new scam targeting computer users, is on the rise in the U.S. The scam prompts its targets to click on a link to fix a problem.
Skydweller is the world's largest unmanned solar-powered aircraft that can stay airborne for weeks, and even months, without refueling.
The U.S. government said it is investigating after People's Republic of China hackers targeted commercial telecommunications service providers in the U.S.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
Scammers are using increasingly sophisticated ways to lure unsuspecting victims, and some are impersonating an email help desk or support team.
You can unsend or edit your iMessages with the latest iOS updates. Kurt the CyberGuy explains how you can save yourself some potential embarrassment.
Kurt “CyberGuy" Knutsson says there’s been a rise in cybercriminal services using hacked police and government emails to send subpoenas and data requests to U.S. companies.
Kurt “CyberGuy" Knutsson discusses how Axiom Space and luxury designer Prada are collaborating on NASA's Artemis III spacesuit design.
Voice assistants may cause confusion across devices. Tech expert Kurt “CyberGuy" Knutsson offers some solutions to fix it.
Shameless scammers trick veterans into giving personal info or cash. Tech expert Kurt “CyberGuy" Knutsson explores five common scams.
Tech expert Kurt “CyberGuy" Knutsson helps you honor our heroes with these powerful podcasts, audiobooks and documentaries this Veterans Day.

Selecting a Personal Digital Assistant

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

Buying a Personal Computer (PC)

For many people the computer industry is a seeming unsolvable... Read More

5 Steps to Remove Spyware for Free

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

Seven Things to Consider When Choosing a PDA

In the early days, Personal Digital Assistants (PDAs) were not... Read More

Are You Well Protected?

Winter?the official start of the cold and flu season. Though,... Read More

Use Your Computer For Your Entertainment Needs

By using your computer today you can find a lot... Read More

Is Online DVD Rental or Pay-Per-View the Best Way to Get Your Movies?

With renting methods such as online DVD rental and pay-per-view,... 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

How To Increase Computer Speed And Performance - Great Tips & Tricks

Many computer users are worried about their computer's performance. After... Read More

Basic Computer Maintenance

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

The Newbies Guide to Personal Computer Maintenance

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

Connect Your IPAQ to Linux

Choose Not To ConformPalm pilots and pdas are constantly growing... Read More

Is Desktop Search Deserving?

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

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

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

Registry Tools Demystified

Not sure what Windows registry is or how it works?... Read More

Your Home is in Your Hands

I would like to tell you about a magic home.... 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

A Lesson in HTML

As the owner of a Country Mall and Top Site... Read More

An All Too Familar Pain

Lost & Found for the 21st CenturyIn today's hectic world... Read More

Cisco Certification: Building Your Own Home Lab, Part I

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

Lives With Games Tradition and Industry

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

MobiTV and Visual Stimulation Imput for Personal Cognitive Performance

Stimulus thru Caffiene or Visual Input?It appears that the Mobi... Read More

Choosing a Portable MP3 Player: Part 1

MP3 players are everywhere! It seems that the number of... Read More

How To Safeguard Your Computer?

The following practice, if done regularly, may help you to... Read More

Favorite Things To Do With Home Technology

Save money. Save 10-15% on utility bills per month. Work... Read More

Antigo wedding limo ..