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.

expert residential cleaners Park Ridge ..
In The News:

The Omoway Omo X features a modular design, obstacle detection and emergency braking, and can drive itself to charging stations, signaling the future of smart urban mobility.
PillTrek, a tiny wireless smart capsule created by Caltech, provides real-time gut health data by detecting electrolytes, metabolites, glucose, hormones, pH levels and temperature.
Learn to spot fake bank text messages with five warning signs of Chase phishing scams, including suspicious links and urgent deadlines designed to steal your account information.
Robot coyotes built on motorized cars reaching 20 mph are being deployed at military airfields as an innovative solution to wildlife threats to aircraft safety.
Explore Google Drive's strengths and weaknesses as a backup solution, including its encryption security, privacy concerns and comparison to dedicated backup tools.
As environmental challenges grow, underwater habitats gain momentum with DEEP's Vanguard, supporting short missions and Sentinel designed for 28-day stays at depth.
The new Urban Eyes safety vest features dual cameras, reflective panels and a wireless remote to deter harassers and provide peace of mind for outdoor workouts.
The $200 billion data broker industry tracks your personal details across websites, posing privacy risks that can be mitigated through data removal services.
UCC Holding has teamed up with COBOD to create desert-inspired, 3D-printed schools in Qatar, featuring dune-like wavy walls impossible with conventional construction.
Darkling beetles equipped with microchip backpacks can be guided through disaster areas using electrical signals, offering a biological solution for search and rescue.
Scammers use number porting to take control of outdated landlines still connected to financial accounts, bypassing two-factor authentication to steal funds while avoiding detection.
Twitter co-founder Jack Dorsey developed Bitchat, a privacy-focused messaging app that works offline via Bluetooth without requiring email, phone numbers or personal data.
Google is making a push to ensure its AI, Gemini, is tightly integrated with Android systems by granting it access to core apps like WhatsApp, Messages, and Phone.
A new green energy system is set to change how we capture clean power, and it all starts with the ocean. French startup Seaturns has designed technology that taps into the natural motion of the sea.
Cybersecurity researchers are warning that hackers have started exploiting flaws in chatbots to carry out AI phishing attacks.
Google has just made it easier than ever to regain control of your inbox with Gmail's new Manage Subscriptions tool.
Despite the benefits, residential power saver programs come with several potential drawbacks and concerns that have been raised by both customers and experts.
The Blackdot AI tattoo machine is quiet. It's steady. And according to early users, it hurts a lot less.
Attackers have started to exploit the very signals that users assume will keep them safe when it comes to add-ons to improve productivity or entertainment.
Located on Cerro Pachón in Chile, the world's most powerful digital camera is set to transform how we see the universe.
The innovative DQ Tower stands 28 feet tall with 420 square feet of living space, featuring floor-to-ceiling windows and premium amenities in a prefabricated design.
A new mmWave imaging system allows warehouse robots to scan and create 3D models of objects inside sealed containers, potentially revolutionizing shipping processes.
Family fraud endangers seniors when relatives exploit their trust, but removing personal data online and monitoring identity can prevent financial harm to aging parents.
A study analyzing 500,000 customer service interactions shows chatbots struggle with complex issues while human agents excel at matching customer communication styles.
Aigen's Element robot uses solar power and AI to provide farmers with a sustainable alternative to herbicides, working efficiently in cotton and soy fields.

DVD The Ultimate Consumer Digital Media

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

SOBIG.F Virus Promises Ill Be Back

On 21 August 2003 Symantec Security Response upgraded the W32.SOBIG.F... Read More

404 Error Pages: What Are They And How Do You Create One?

We've all seen them, you've been browsing a website and... Read More

Troubleshooting Computer Freeze (Lockup) Problems

How familiar does the following sound? Your computer was working... Read More

JVCs First Three-Layer Combo-Disc Blu-Ray / DVD

JVC developed and used a high-performance reflective film to produce... Read More

iPod users get the picture

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

Basic Computer Thermodynamics

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

CCNA 640-801 Certification Primer

The Cisco Certified Network Associate (CCNA) Certification is meant for... Read More

Portable DVD Players - Things To Know

In my humble opinion nothing makes a long trip easier... Read More

5 Ways to Speed Up Your PC

No matter how fast your processor and regardless of how... 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

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

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

Enhanced Web Browsing With Toolbars

As the Web grows more crowded and just plain "noisy"... 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

Selecting the Perfect Big Screen TV

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

Taking Advantage of the iPod Experience

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

System File Checker - A Maintenance Utility

System File Checker is a great utility that is typically... Read More

Things You Can Do To Speed Up Your Computer

Upgrading your processor will always speed up your computer, but... Read More

Have You Invited A Spy Into Your Computer?

Yes, it's true. You may have inadvertently invited a spy... Read More

Is DVD Storage An Attractive Alternative For Your Computer Backup?

If you have a computer for home use or for... Read More

Are You Putting Off Productivity?

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

Deleting and Destroying Data Forever

Peoples' private information needs to stay private, even after it... Read More

Internet Explorer Shortcuts for Mouse-o-phobes

If you're like me, you occasionally find the ubiquitous mouse... Read More

Lightning Season: How to Avoid Data Loss

We have just entered the time of year that most... Read More

If You are the Only User on Your Windows XP Computer, Do You Think One User Account Is Enough?

I am going to assume that you are running windows... Read More

green cleaning service Park Ridge ..