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.

04/28/2024 The Best Binoculars to Zoom In on Real Life Whether you’re bird-watching or baseball-spotting, we break down prices and specs to find the best pair for you. more info

04/28/2024 The Best Sleeping Bags for Every Adventure Whether you’re climbing peaks or taking the family to the local park, we’ve found the best sleeping bags for every temperature, budget, and camping expedition. more info

04/28/2024 Our Favorite Digital Notebooks and Smart Pens These nifty tools combine the ease of jotting notes by hand with the power of saving them digitally. more info

04/28/2024 The Best Password Managers to Secure Your Digital Life Keep your logins locked down with our favorite password management apps for PC, Mac, Android, iPhone, and web browsers. more info

04/28/2024 How to Get Free Kindle Books With Your Library Card All you need is an internet connection, a library card, and a good ebook reader to dive into your next page-turner. more info

04/28/2024 I Tried These AI-Based Productivity Tools. Here’s What Happened Hoping to make life easier, I tested six AI-powered tools meant to help me write better and work smarter. more info

04/28/2024 The Mysterious ‘Dark’ Energy That Permeates the Universe Is Slowly Eroding Physicists call the dark energy that drives the universe “the cosmological constant.” Now the largest map of the cosmos to date hints that this mysterious energy has been changing over billions of years. more info

04/27/2024 The Best Sleeping Pads for Camping, Backpacking, and Travel Whether you’re snoozing in a campground or schlepping up to an alpine valley, these are the best pads we’ve found for resting your weary bones. more info

04/27/2024 The Best Robot Vacuums to Keep Your Home Clean Whether you’re up against pet hair or you want to splurge on a high-end laser-guided robot vacuum, we have the perfect pick for you. more info

04/27/2024 Get the Most Out of Your iPad With These Accessories These are some of our favorite stands, cases, keyboards, and styli, no matter which Apple tablet you have. more info

04/27/2024 7 Spring Albums That You Don’t Need to Fight About Online New music from Maggie Rogers, Tyla, Brittany Howard, and SchoolBoy Q showcase distinct artistic evolutions. more info

04/27/2024 Autocomplete Interview Autocomplete Interview - Is Ice Cube a nice guy? Do astronauts really drink their own pee? Does Gerard Butler still surf? The internet searches for answers and WIRED goes right to the source for the answer. more info

04/27/2024 Meta’s Ray-Ban Smart Shades Get a Fresh Blast of AI Plus: Leaked details tell us more about the new Google Pixel 8A, Freitag’s environmentally conscious bag is entirely recyclable, and it’s time to unpack a whole bunch of tech acronyms. more info

04/27/2024 1 in 3 Americans Live in Areas With Dangerous Air Pollution Climate change is increasing the number of days people are exposed to hazardous pollution, affecting already disadvantaged communities the most. more info

04/27/2024 School Employee Allegedly Framed a Principal With Racist Deepfake Rant This week in cybersecurity news: Google holds off on killing cookies, Samourai Wallet founders get arrested, GM stops its driver surveillance program, and a school principal's racist rant is revealed to be a deepfake. more info

04/27/2024 Russia Vetoed a UN Resolution to Ban Space Nukes A ban on weapons of mass destruction in orbit has stood since 1967. Russia apparently has other ideas. more info

04/26/2024 Roborock’s Robot Vacuums—Including WIRED’s Top Pick—Are on Sale Right Now More like Robot Rock, am I right? (Sorry.) These are some of the best dust busters around, and they’re cheaper than usual. more info

04/26/2024 Tesla Autopilot Was Uniquely Risky—and May Still Be In an investigative report into crashes and deaths associated with Tesla Autopilot, federal regulators concluded that the system lacked standard protections. more info

04/26/2024 The 33 Best Shows on Amazon Prime Right Now From "Mr. and Mrs. Smith" to "Fallout," these are our picks for what you should be watching on the streamer. more info

04/26/2024 The 17 Best Movies on Amazon Prime Right Now From "Road House" to "Bottoms," these are the must-watch films on the streamer. more info

04/26/2024 Decades of Garry’s Mod Nintendo Uploads Are Disappearing Nintendo is once again flexing its copyright muscles by filing takedown requests for user-generated content on the popular game platform. more info

04/26/2024 The Best Lubes for Every Occasion For the most sensitive parts of the human body, friction is the enemy. Here’s how to keep it at bay. more info

04/26/2024 Which Govee Smart Lighting Kit Should You Buy? Govee makes some of the best affordable smart lights, but its enormous range can be overwhelming and confusing. Here’s how to choose the right fit for your home. more info

04/26/2024 The Best USB-C Cables for Your Phone, Tablet, or Laptop Unravel the tangled world of cords and find the ones you need to charge your gadgets and transfer data. more info

04/26/2024 The Best Car Phone Mounts and Chargers These mobile accessories will make your smartphone a better—and safer—road trip companion. more info