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.

Barrington South Lincoln Stretch rentals .. Lockport Chicago limo O’Hare
In The News:

A newly discovered security flaw with your iPhone or iPad could leave your personal information vulnerable to hackers. Kurt the CyberGuy explains.
Modeled after a traditional bell-shaped skirt worn by women in Kosovo, a theater's roof is draped entirely by solar panels to generate renewable energy on-site.
Kurt the CyberGuy explains how to protect yourself from malicious Chrome extensions that can steal sensitive information like password managers, crypto wallets and banking apps.
Stellantis' innovative STLA AutoDrive 1.0 system allows drivers to safely take their eyes off the road and their hands off the steering wheel.
Here are some travel hacks to save money on your next vacation including setting up price alerts, remaining flexible and checking for 24-hour price drops.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
One report claims at least 25 million people were targeted by infostealer malware between the beginning of 2023 and the end of 2024, capturing bank card details and passwords.
The LaserWeeder G2 uses lasers, artificial intelligence and advanced robotics to offer an alternative to chemicals traditionally used in farming.
Tech expert Kurt “CyberGuy" Knutsson says a malware app called SpyLend is posing as a financial tool, hitting 100,000-plus downloads.
Tech expert Kurt “CyberGuy" Knutsson says Clone Robotics’ Protoclone mimics humans with 1,000 muscles – too human for comfort.
Tech expert Kurt “CyberGuy" Knutsson urges you to use Apple’s App Privacy Report to see what your apps are really up to.
Tech expert Kurt “CyberGuy" Knutsson talks about how to smoothly transfer your data from an old laptop to a new one using cloud storage or external drives.
Kurt the CyberGuy offers tips to safeguard your personal information as scammers become more sophisticated with scam texts and other methods.
WeRide's unmanned electric delivery van, the Robovan W5, has 194 cubic feet of cargo space and can carry payloads up to about 2,205 pounds
Kurt the CyberGuy recommends deleting 16 browser extensions immediately in response to a report saying more than 3.2 million web users were affected by a security breach.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
A robotics firm in China claims a robot has performed the world's first humanoid robot front flip, which is significantly more difficult than a backflip.
A digital ID may be even more secure than a physical ID. Storing your driver's license in your Apple Wallet is an ideal solution for commuters.
A cybersecurity firm has shed some light on just how common tax scams have become. Kurt the CyberGuy offers his advice for how to protect your refund.
Lifesize Plans offers immersive walk-throughs that allow customers the opportunity to tour a home's design before construction actually begins.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
A man invested $4 million in bitcoin and lost it all with a single click when he was targeted by a vishing scam, which uses phone calls to trick people.
Expandable's Touchdown is a designer trailer that promises a luxury off-grid living experience and unfolds into a home in about 15 minutes.
Major employee screening company data breach affects 3.3 million people. Tech expert Kurt “CyberGuy" Knutsson explains what happened and gives five ways to protect your data.
Kurt “CyberGuy" Knutsson reveals mind-blowing tech that gives you sunlight on demand.

Favorite Things To Do With Home Technology

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

The Benefits of the New Firefox Browser

You probably heard of the new Firefox browser version 1.0... Read More

More Cool Web Tricks

If you ask most people what frustrates them most about... Read More

How Do MP3 Players Work? Digital Audio Technology Revolutionizes Music Enjoyment

Millions have enjoyed recorded music since 1877 when Thomas Edison... Read More

Cisco Certification: Building Your Home Lab, Part II

In the first part of this home lab tutorial, CCNA... Read More

5 Ways to Speed Up Your PC

No matter how fast your processor and regardless of how... Read More

Cisco Certification: The Most Important Cisco Study Youll Ever Do

All of us are familiar with the pyramids of Egypt.... Read More

How Does a Palm Pilot Work?

Just The Facts, Ma'mPalm pilots are all the rage now.... Read More

The Benefits of Open Source

If you are a building a website or a forum,... Read More

How to Place Home Theater Speakers

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

Looking For an MP3 Player?

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

How to Save an Image in a SQL Server Database?

Most of the web applications have a lot of images... Read More

Tips For Finding Great Deals On Computer Accessories & Supplies

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

Build Your Own Computer

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

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

So you've got some spyware, ad-ware, or viruses on your... Read More

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

Since the DOS days, batch files have been one of... Read More

Taking Advantage of the iPod Experience

Music lovers have been carrying around radios and other bulky... Read More

Flow Text Around a Graphic in Microsoft Word

Flow Text Around a GraphicQuestion: I have inserted a photo... Read More

MCSE 70-290 Certification Primer

Microsoft Certifications are one of the most widely acclaimed, pursued,... 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

Cache In Your Chips And Get A Bus!

Computer related terminology could sometimes be daunting to newcomers. These... Read More

Classification of Computers

Computers are available in different shapes, sizes and weights, due... Read More

Cisco Certification: Building Your Own Home Lab, Part I

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

Virtual Memory - What is It?

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

Repairing A Corrupt .RAR/.ZIP Archive

Step 1:Open WinRAR and browse to the folder with your... Read More

Downers Grove limo Chicago ..