Home
>
Personal Technology
>
How to Use SQLXML to Retrieve Data from SQL Server Database?

town car ride to ohare Marengo Park Ridge taxi to Midway Naperville south of 95 limo Midway Lincoln Stretch limo rentals Deerfield travel from O'Hare Carpentersville .. Drug testing

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.

limousine chicago service

In The News:

The Boy Scouts of America is trying to recruit a new generation of kids to join its troops with high-energy, high-tech activities that include thrill-inducing zip lines at a new adventure camp, apps and a television show.

The next iPhone or iPad from Apple is surely around the corner, and Google is getting into everything from cloud computing to car making. But what the cards hold for $100 billion behemoth Facebook is far from clear, experts say.

Rumors of the hairy humanoid known variously as the yeti, bigfoot and sasquatch have persisted for decades. Now scientists are hoping to make more of a case for the creature -- with the help of genetic testing, Reuters reported.

Scotty has finally been beamed up. The ashes of the actor James Doohan, who played Scotty on the 1960s television series "Star Trek," were launched to space this morning (May 22) on a SpaceX Falcon 9 rocket.

Cows are a red herring. The most dangerous potential source for methane release lies underneath thinning permafrost and glaciers in the Arctic. Ecologists have just mapped the seeps where methane is bubbling up, and they found more than 150,000 of them.

At a time when black magic was relatively common, two curses involving snakes were cast, one targeting a senator and the other an animal doctor, says a Spanish researcher who has just deciphered the 1,600-year-old curses.

Presidents Barack Obama and Bill Clinton, U2 frontman Bono and more honored the memory of late Apple founder Steve Jobs Monday in New York City at the 16th annual Webby Awards.

Humans' close relationship to dogs has so far obscured their history so much that it's not yet possible to use genetic data to tease out the details of their domestication, new research indicates.

A Jurassic mom's almost certainly painful death is perfectly preserved in a rare fossil skeleton, one of the many unique items that will go on display in the Houston Museum of Natural Science's $85 million dinosaur hall when it opens to the public June 2. We take a first peek at the exhibit.

A Jurassic Mom's almost certainly painful death is perfectly preserved in a rare fossil skeleton, one of the many unique items that will go on display in the Houston Museum of Natural Science's $85 million dinosaur hall when it opens to the public June 2.

The rise of Kickstarter's crowdsourcing platform means consumers can now vote with their dollars, investing in unique new projects that they believe in, from smartphone watches designed from e-paper to coffee joulies that keep your drink at the perfect temperature. Here are the 12 most funded design projects.

Jodie Foster may have seen proof of alien lands in the 1997 Robert Zemeckis film "Contact," but the real life astronomer the filmmakers based their sci-fi odyssey on didn't find so much as a tentacle.

A first-of-its-kind commercial supply ship rocketed toward the International Space Station following a successful liftoff early Tuesday, opening a new era of dollar-driven spaceflight.

Many scientists say it's just a matter of time before we find evidence it exists, and now, anyone can get in on the hunt -- as long as they have a computer.

The private rocket company SpaceX is officially "go" to make a second try at launching its unmanned Dragon capsule early Tuesday, May 22, from Florida's Space Coast.

The Supreme Court refused Monday to hear an appeal from a Boston University student who was slapped with a $675,000 penalty for illegally downloading 31 songs and sharing them on the internet.

The European Union has given Google "a matter of weeks" to propose remedies to antitrust concerns arising from its alleged dominant position in the online search market.

A Maryland student was awarded the top prize at the Intel International Science and Engineering Fair on Friday for developing a urine and blood test that detects pancreatic cancer with 90 percent accuracy.

Microsoft recently announced plans to strip the Windows interface to its basics -- flattening surfaces, removing reflections, and scaling back distractions. Here's a brief look at the Windows interface over its 27-year history -- and how it will look tomorrow.

Microsoft's So.cl social network opened to the public at large this weekend. The experimental research project combines social networking and search, the company said -- and the company swears it's not meant to compete with Google+ or Facebook.

How to Use SQLXML to Retrieve Data from SQL Server Database?

Personal Technology Personal Technology