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.

after renovation cleaning Wilmette ..
In The News:

SessionReaper vulnerability hits Magento and Adobe Commerce stores, compromising 250+ sites in one day. Hackers steal data and hijack shopping sessions.
Master essential parental controls and digital safety tools to protect your kids online. Learn screen time limits, location settings and privacy controls every parent needs.
Cybersecurity expert shares six essential steps to protect against dark web threats, including data removal services, password managers and antivirus software.
NVIDIA GPU space mission could revolutionize cloud services by enabling orbital data centers that make apps, games and AI tools faster and more efficient.
Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.
Microsoft warns Windows 10 users face serious security risks as 90% of ransomware attacks target unsupported systems. Learn why upgrading to Windows 11 is crucial.
Nike unveils Project Amplify, revolutionary motorized shoes developed with Dephy that add powered assistance to every step for runners and walkers.
Cybersecurity experts warn about one of the largest credential compilations ever found, urging users to check "Have I Been Pwned" and change passwords immediately.
Quick iPhone and Android battery optimization techniques help your device stay powered all day by turning off hidden features that secretly drain power in the background.
Kodiak Driver autonomous truck achieves perfect 98 safety score, matching top human fleets in groundbreaking AI evaluation by Nauto's VERA system.
New 401k catch-up contribution rules in 2026 will change taxes for high earners over 50. Learn how scammers exploit these changes and protect your retirement savings.
Kurt Knutsson's guide covers social media privacy protection through location settings, account privacy controls and two-factor authentication to prevent scams and data breaches.
Revolutionary retinal implant restores central vision in 80% of patients with advanced macular degeneration, offering hope where treatments once only slowed blindness.
Learn how to use passkeys on Windows and Mac computers without cameras or fingerprint readers. Discover secure authentication methods that replace passwords.
Tesla's FSD v14.1.2 update reintroduces Mad Max mode, enabling higher speeds and more frequent lane changes than the standard Hurry profile setting.
A phishing email scam targeting American Express customers shows how cybercriminals use fake urgent messages to steal personal and financial information.
Facebook's new Meta AI feature analyzes your camera roll photos to create polished collages automatically, but requires cloud processing and raises privacy concerns.
A New Jersey teenager filed a major lawsuit against AI/Robotics Venture Strategy 3 Ltd. over ClothOff, an AI tool that created fake nude images from her social media photos.
Microsoft reports Storm-2657 cybercriminals sent phishing emails to 6,000 addresses at 25 universities to steal payroll credentials and redirect funds.
Astronomers have discovered asteroid 2025 SC79, a skyscraper-sized space rock orbiting the sun in just 128 days. the second-fastest known.
The Fox News AI Newsletter delivers the latest developments form the world of artificial intelligence, including the technology's challenges and opportunities.
A cyberattack on SimonMed Imaging exposed personal information of 1.2 million patients, including medical records, financial details and identity papers.
Spotify's managed accounts for kids under 13 now available in at least seven countries, allowing parents to filter and block explicit content and songs.
Friendly text conversations about BBQs and social events can lead to WEEX gold trading scams that target older adults with fake investment opportunities.
California company Skyeports creates self-healing glass spheres from Moon regolith that generate solar power and support plant growth for sustainable lunar living.

How To Search Your PC With Google Desktop

In this era of Internet, most people are frequent users... Read More

Web Standards

HTTP ProtocolThe web is run on port 80. You are... Read More

Tips for Buying a PC

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

Bluetooth Technology: Tips for Buying Headsets or Headphones

The technological horizon has always got something new to offer,... Read More

Why Build Your Own Computer System

Have you ever thought about building your own computer system?... Read More

How To Become A True CCNA

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

15 Good Programming Habits

1. Before sitting down for coding, you must have formal... Read More

The Help Desk

When you think of a help desk, what do you... Read More

Portable Technology: Getting to Know the Treo

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

4 Computer Money-saving Tips

Tip #1 -- Rebates: A rebate is not always a... Read More

HTML Explained: Part 2

Get started creating web pages using text files and HTML... Read More

Tips For Getting Technical Support Help Online

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

PDA Bible Free Download

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

Why Get a Microsoft MCSE Certification?

In the years of the dot com boom and bust,... Read More

Best PC Pocket GPS

Stop Getting LostOne of the greatest uses for a pocket... Read More

Selecting a Personal Digital Assistant

A Computer in Your HandCarrying around an address book and... 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

Digital Cameras + Photo Printers = Quality Instant Photographs

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

Is Digital Camera Technology Making Film Obsolete?

Perhaps not yet, but the handwriting might be on the... Read More

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

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

Ergonomics and Healthy Computing - Positioning Your Body For Maximum Comfort At Your Computer

Do you remember the old saw about how computers would... Read More

Got Virus?

GOT VIRUS? Your Data is NOT lost forever!In the wake... Read More

Email Management

If you utilize a computer at home or work it... Read More

The Best MP3 Players Under $100

You don't have to fork out $250 for a super-diggy-whizbang... Read More

Healthy Webmasters Choose Ergonomic Computer Products

Webmasters usually sit at their computers much longer in a... Read More

interior house cleaning Wilmette ..