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.

housekeepers near Buffalo Grove ..
In The News:

Analysts at Silent Push have uncovered a widespread scam that uses counterfeit retail websites to steal the credit card information of online shoppers.
Chrome users need to update their browsers immediately as Google addresses a critical vulnerability that hackers are actively exploiting. Additional security measures are recommended.
A study found teens glance at phones for dangerous two-second intervals while driving, using devices primarily for entertainment, texting and navigation despite crash risks.
Security researchers uncovered a flaw in Windows 11 that allows attackers to disable Secure Boot using Microsoft-signed tools, requiring manual updates to protect against bootkits.
Carnegie Mellon's noninvasive brain technology allows users to move robotic fingers by thinking about the motion, offering new possibilities for people with motor impairments.
Multi-factor authentication (MFA) adds an extra layer of protection to your accounts. Instead of relying only on a password, MFA requires you to verify your identity using two or more methods.
Scientists from Duke, Harvard, and the University of Otago have unveiled a game-changing tool called DunedinPACNI. It uses a single AI brain scan to reveal how fast a person is biologically aging.
A disturbing wave of fake agent phone scams is sweeping across the United States, catching people off guard and draining their savings.
Emojis aren't just playful add-ons; they're powerful tools for building stronger connections in our increasingly digital world.
Google has taken a major step toward the future of clean energy by partnering with Commonwealth Fusion Systems (CFS), an MIT spin-out working to build one of the world’s first commercial fusion reactors.
If you create and share art online, you might have seen messages warning that Facebook's parent company, Meta, claims the right to use or even sell anything you post, whether it's pictures, poems, or artwork.
Cybercriminals hit Qantas in a major data breach that exposed information from up to six million customers.
Having reliable, loud, and timely emergency alerts on your phone or device is important because when severe weather strikes, every second counts.
Here's how your daily brew is becoming the foundation for greener buildings.
Artificial intelligence (AI) and large language models (LLMs), such as ChatGPT, are transforming how we learn. But what does this mean for AI and learning retention?
Modern AI data centers use much more electricity than traditional cloud servers. In many cases, the existing power grid cannot keep up. One innovative solution is gaining traction: repurposed EV batteries for AI data centers.
Microsoft 365 and Outlook users are being targeted by a tactic that injects fake billing alerts directly into their calendars.
Researchers are now showing us that old smartphones as data centers could be the next big thing in sustainable tech.
Scientists have created micro-robots for sinus infection treatment that can enter the nasal cavity, eliminate bacteria directly at the source, and exit without harming surrounding tissue.
Let's examine how your data is collected in everyday life, who is buying and selling it, what happens to it afterward, and, most importantly, what you can do to protect yourself.
Researchers at the University of Sheffield in the U.K. developed small robots called "Pipebots" that can travel inside water pipes to find and potentially repair leaks, all without any excavation.
A groundbreaking new study has uncovered disturbing AI blackmail behavior that many people are unaware of yet.
Four teams of autonomous humanoid robots competed in China's first AI soccer tournament, demonstrating advanced capabilities in ball detection and independent decision-making.
The ID. Buzz autonomous van features self-driving technology with 13 cameras, nine LiDAR units and five radars after Volkswagen partnered with Hamburg and Uber for 2026 deployments.
Social Security phishing scams use urgency and impersonation to steal personal data. Learn how to spot fake SSA emails and implement 10 protective measures.

PDA Bible Free Download

Your Bible At Your FingertipsIt is fairly easy to find... Read More

Is DVD Storage An Attractive Alternative For Your Computer Backup?

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

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

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

Flash Your nVidia Video Cards BIOS

You will need the following:1. 1.44MB FDD (floppy)2. NVFLASH utility... Read More

Occupational Therapy and PDAs

Occupational Therapy Made EasierMedical downloads for the pda have improved... Read More

How To Become A True CCNA

I've worked my way from the CCNA to the CCIE,... Read More

What Does That Error Message Really Mean?

Surf the 'Net for about 10 minutes and chances rate... Read More

Portable Technology: Getting to Know the Treo

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

Digital Cameras + Photo Printers = Quality Instant Photographs

In the 1950's and 1960's Polaroid's instant cameras were all... Read More

Computer Performance Tips

Introduction In this article I will clearly spell out the... Read More

Classification of Computers

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

Compare MP3 Players for the Coolest Features and Sweetest Deals

How can you compare mp3 players to be sure you're... Read More

SOBIG.F Virus Promises Ill Be Back

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

Cisco CCNA Certification: Five Things To Do DURING The Exam

There are plenty of articles out there about how to... Read More

Bluetooth Headphones For Your PDA

Nothing is worse than having to negotiate all kinds of... Read More

Protect Your PC from Viruses, Worms & Trojans

Each of the items in the checklist below is part... Read More

Lives With Games Tradition and Industry

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

Tips For Getting Technical Support Help Online

Sometimes your PC will start acting strange for no apparent... Read More

Desktop Security Software Risks - Part 2

This is the third in a series of articles highlighting... Read More

How to Place Home Theater Speakers

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

Put Order and Information into File Names

Unless you place your faith in internal search engine software... Read More

Does Microsoft Show Hackers How To Attack?

After another security hole recently surfaced in Microsoft's Windows operating... Read More

Get Ahead When You Build Your Own Computer

If you've been kicking around the idea of building your... Read More

The Benefits of the New Firefox Browser

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

Basic Computer Thermodynamics

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

custom home cleaning Mundelein ..