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.

best cleaning company Highland Park ..
In The News:

Smart home hacking fears overblown? Expert reveals real cybersecurity risks and simple protection tips to keep your connected devices safe from hackers.
MIT develops needle-free glucose monitor using light technology. Revolutionary device could replace painful finger pricks for diabetes management.
The ClickFix campaign disguises malware as legitimate Windows updates, using steganography to hide shellcode in PNG files and bypass security detection systems.
Researchers from Osaka Metropolitan University designed a 21-foot dome that combines aquaculture and hydroponics to create a self-sustaining urban food system.
The Fox News AI Newsletter gives readers the latest AI technology advancements, covering the challenges and opportunities AI presents.
ChatGPT data breach exposes personal info of users through partner Mixpanel. OpenAI confirms names, emails compromised in security incident.
Android rolls out Emergency Live Video for 911 calls, letting dispatchers see real-time scenes during emergencies. Great for holiday travel safety.
Malicious Chrome and Edge extensions collected browsing history, keystrokes and personal data from millions of users before Google and Microsoft removed them.
Google's new Call Reason feature lets Android users mark calls as urgent before dialing, displaying an urgent label to recipients using Phone by Google app.
Medical history made as surgeons successfully restore sight to legally blind patient using world's first 3D printed corneal implant grown from human cells.
Data brokers aggressively collect your holiday shopping data to fuel scams and targeted ads. Learn how to delete your digital profile before 2025 starts.
Scammers are sending fake MetaMask wallet verification emails using official branding to steal crypto information through phishing links and fraudulent domains.
Learn what background permissions, push notifications, security updates, auto-join networks and app refresh mean to better manage your phone's privacy settings.
Criminals test stolen data by applying for deposit accounts in victims' names to prepare bigger attacks. Learn why banks won't share fraud details.
New study of 10,500+ kids reveals early smartphone ownership linked to depression, obesity, and poor sleep by age 12. Earlier phones mean higher risks.
A phone phishing attack compromised Harvard's alumni and donor database, marking the second security incident at the university in recent months.
AutoFlight's zero-carbon floating vertiport uses solar power to charge eVTOL aircraft while supporting emergency response, tourism, and marine energy maintenance.
A new phone return scam targets recent buyers with fake carrier calls. Learn how criminals steal devices and steps to protect yourself from this fraud.
New Anthropic research reveals how AI reward hacking leads to dangerous behaviors, including models giving harmful advice like drinking bleach to users seeking help.
The Fox News AI Newsletter gives readers the latest AI technology advancements, covering the challenges and opportunities AI presents.
Holiday email scams, including non-delivery fraud and gift card schemes, spike in November and December, costing victims hundreds of millions, the FBI says.
Holiday visits offer the perfect opportunity to help older parents with technology updates, scam protection and basic troubleshooting skills for safer digital experiences.
Swiss scientists create grain-sized robot that surgeons control with magnets to deliver medicine precisely through blood vessels in medical breakthrough.
Researchers exploited WhatsApp's API vulnerability to scrape 3.5 billion phone numbers. Learn how this massive data breach happened and protect yourself.
Travel companies share passenger data with third parties during holidays, but travelers can protect themselves by removing data from broker sites and using aliases.

How to Protect your PC from Spyware in the Cyber Age

Wouldn't you be shocked to find that your personal sensitive... Read More

Spyware Remover

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

More Cool Web Tricks

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

Looking For an MP3 Player?

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

Virtual Memory - What is It?

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

How To Remove One Operating System from a Dual Boot Windows XP Computer

In Windows Xp, you can install two operating systems on... Read More

Quick System Restore with ASR Backups

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

What Does That Error Message Really Mean?

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

Mail-merging: The Principles

About mail-mergingMail-merging is the process of merging variable data and... Read More

Registry Tools Demystified

Not sure what Windows registry is or how it works?... Read More

5 Ways to Speed Up Your PC

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

Lightning Season: How to Avoid Data Loss

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

Maintain Your Computer ? Keep Your Business Running

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

Microsoft Great Plains Dexterity Customizations

Microsoft Business Solutions is on the way to popularize it's... Read More

iPod - Learn How It Can Solve Everyday Problems

All over the world, people carry with them walkmans and... Read More

Font Organizers Review, Part I

Do you know how many fonts are currently installed on... Read More

Cisco Certification: Building Your Own Home Lab, Part I

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

Is Your Web Browser Putting You At Risk?

It's free, it comes with Windows and it's used by... Read More

EDTV vs HDTV

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

Your Affiliate Business - Peripherals, Software, Computers

I have always been interested in computers, but in the... Read More

The Advantages of Portable MP3 Players

With their solid-state technology, compact size, and abundant memory, portable... Read More

A Beginners Guide to Avoiding Viruses

"Aaaaaahhhhhh! I've been invaded by a virus!" Getting a virus... 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

Why Using an Ad Blocker Can be a Bad Idea

Ad Blocking is getting to be a common sport on... Read More

high-end home cleaning Winnetka ..