Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in /home/a26f9f83/public_html/articles/includes/config.php on line 159
How to Use SQLXML to Retrieve Data from SQL Server Database? > NetSparsh - Viral Content you Love & Share

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.

In The News:

This RSS feed URL is deprecated, please update. New URLs can be found in the footers at https://news.google.com/news

GeekWire

Seattle startup vets take on Google with Helm, a new $499 personal email server
GeekWire
It's a bold bet that aims to provide comfort at a time when privacy and security issues related to personal data hosted by big tech companies in the cloud are top of mind. The idea comes from Giri Sreenivas and Dirk Sigurdson, two entrepreneurs who ...
Are consumers worried enough to buy a personal computer server?Hindustan Times
Are consumers worried enough to buy a personal server?TechCentral

all 26 news articles »

New York Times

An Eye for Photography, but a Google Suite for Events
New York Times
How do New York Times journalists use technology in their jobs and in their personal lives? Whitney Richardson, an events manager for The Times in London, discussed the tech she's using. Q. What tech do you use to put together events? A. Over the past ...


Madison.com

The case for rotating out of major tech stocks
Madison.com
CEO of Exponential ETFs Phil Bak tells CNN's Richard Quest why he thinks it's time for investors to consider taking some profits from FAANG and putting their money elsewhere. Tags. Wire · Cnn · Facebook · Twitter · Email; Print; Save. View comments ...


USA TODAY

Apple makes it easier to download your personal data
USA TODAY
A few months ago, if you wanted to find out what kind of personal information Apple had collected on you, you needed to navigate to Apple's privacy website, locate a hard-to-find tab to request the info and await its arrival. That changes today, with ...
Apple Is Now Allowing You To Download Your Personal DataWccftech
Privacy - AppleApple

all 40 news articles »

Beware Facebook hoax tricking people into thinking accounts hacked
The Oakland Press
Then, they send a bunch of friend requests to the original account's friend list, to try to scam the person's unsuspecting friends into granting access to their personal information by accepting the request. While account cloning is a real thing, the ...


New York Times

It's Her Job to Get More People to Act Like You Right Now
New York Times
How do New York Times journalists use technology in their jobs and in their personal lives? Millie Tran, a global growth editor for The Times, discussed the tech she's using. Q. You're the first-ever global growth editor at The Times. What does a ...


CNN

Hackers accessed personal information of 30 million Facebook users
CNN
New York (CNN) Almost 30 million Facebook users' phone numbers and email addresses were accessed by hackers in the biggest security breach in the company's history, Facebook said Friday. The attackers accessed even more details on 14 million of ...
Facebook says hackers saw personal info of 14 million peopleNBCNews.com
Facebook hack update: Nearly 30 million users' data stolen. How to find out if you're one of themUSA TODAY
Facebook Hackers Accessed Personal Data From 29M AccountsBillboard
CNET -Facebook -Facebook -Facebook Newsroom
all 1,325 news articles »

New York Times

The Google Pixel 3 Review: Phone's Smarts Shine Through Its AI-Driven Camera
New York Times
Apple and Samsung typically show off glitzy hardware innovations — dual-lens cameras; bigger screens — to mesmerize millions into buying their new smartphones. Google has taken a different approach: It wants to sell people on better software. That ...
YouTube HTML5 Video PlayerYouTube
Translate with Google Pixel Buds - Google SupportGoogle Support
Pixel Buds Real-Time Translations Available on All Google Assistant HeadphonesDroid Life
Google Product Forums
all 378 news articles »

TechCrunch

Journal raises $1.5 million to bring Google-like search to your personal life
TechCrunch
As with most tech products these days, it comes down to how much do you trust the company and how much do you care about your data? And depending on who you are, you may have a stronger threat model — that is, what threats you face based on who ...


The Oakland Press

Purging long-forgotten online accounts: Worth the trouble?
The Oakland Press
Yahoo, for instance, didn't allow users to delete accounts or change personally identifying information they shared, such as their birthday, until pressured to do so after the breach. Even without these hurdles, real life gets in the way. There are ...

and more »
Google News

Password Nightmares

Good Morning Mr. Sampson. Please type in you Personal Identification... Read More

What Does That Error Message Really Mean?

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

How To Buy An MP3 Player

Are you looking for an MP3 player but am not... Read More

Emulation Manual - A Complete Guide on How to Change Your Windows XP to Mac OS X

IntroductionMac OS X is the most technologically advanced operating system... Read More

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

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

Spyware Remover

Your first step in removing dangerous infections from your computer... Read More

Cisco CCNA / CCNP Certification: OSPF ASBRs Explained And Illustrated

When I first started studying for my CCNP, some of... Read More

PC Tools Youd Never Think You Need

Do you use Windows standard uninstall feature? How do you... Read More

The Newbies Guide to Personal Computer Maintenance

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

PDA Bible Free Download

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

Tips for Buying a PC

Buying Your PCBuying a PC that's right for you and... Read More

Maintain Your Computer ? Keep Your Business Running

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

Digital Cameras: How Many Pixels Do I Need?

With the bewildering number of digital cameras on the market,... Read More

Some Thoughts on Choosing a Flash MP3 Player

Flash mp3 players come with an exciting array of features,... Read More

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

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

Build Your Own Computer

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

USB Drives - What to Look For in a USB Device

Those small USB drives have so many names, pocket drives,... Read More

Computer Geeks and Garden Gnomes

First and foremost before I begin my ranting it is... Read More

Use Your Computer For Your Entertainment Needs

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

Enhanced Web Browsing With Toolbars

As the Web grows more crowded and just plain "noisy"... Read More

Deleting and Destroying Data Forever

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

The Many Benefits of Owning a DVR

Has This Ever Happened To You? OK.. it's Friday night,... Read More

EDTV vs HDTV

Confused by EDTV vs HDTV? We don't blame you. The... Read More

Quick System Restore with ASR Backups

ASR (Automated System Recovery) is a feature available on the... Read More

Digital Cameras + Photo Printers = Quality Instant Photographs

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