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

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.

scheduled cleaning services Winnetka ..

In The News:

New iPhone replacement scam uses pressure tactics and fake carrier calls to steal devices from buyers. Criminals claim shipping errors and demand urgent returns.

Amazon Ring's new facial recognition feature sparks privacy controversy as Electronic Frontier Foundation critics argue the AI upgrade expands surveillance risks.

New Android banking trojan Sturnus steals credentials, reads encrypted messages and controls devices.

Denmark's 3D-printed student village proves automation builds 36 apartments faster than traditional methods. Skovsporet project shows housing future.

Discover Android's new Sound Notifications feature that alerts you to smoke alarms, doorbells, and baby cries even when wearing headphones.

New SantaStealer malware reportedly threatens holiday shoppers with password theft. This Christmas-themed info-stealer targets browsers and crypto wallets.

The Christmas season brings a surge in Netflix phishing scams targeting shoppers with fake emails. Stacey P received convincing scam but verified account first.

San Francisco Giants invite Jamie Grohsong to throw ceremonial first pitch at Oracle Park after he learned to play baseball with a bionic hand following an injury.

FBI warns cybercriminals are stealing family photos from social media to create fake proof of life images in virtual kidnapping scams targeting victims.

Instagram's new 'Your Algorithm' tool lets you control your Reels feed in real time. The app now gives you power to customize what videos you see.

Major Marquis fintech breach exposes 400,000-plus Americans' data through unpatched SonicWall vulnerability, with Texas hardest hit at 354,000 affected.

Free up iPhone storage fast by clearing large photos and videos from Messages app. Simple steps for iOS users to delete attachments without losing chats.

Scammers are flooding inboxes with fake tracking alerts that mimic real carriers, exploiting the holiday rush to steal logins and personal data.

The Fox News AI Newsletter brings you the latest news on AI technology advancements and the challenges and opportunities AI presents now and for the future.

Texas family reunites with missing 11-year-old cat Grayson after 103 days using Petco Love Lost's AI photo matching technology and community help.

Tired of AI customer service loops? These insider tricks help you escape "frustration AI" and get real human help when you need it most for urgent issues.

Unlock richer audio from your streaming apps with simple tweaks to volume normalization, equalizer settings, and quality preferences for cleaner sound.

Scammers are sending fake Facebook settlement payout emails that mimic legitimate notices from the privacy settlement administrator to deceive users.

Holiday shopping scams surge as fake refund emails target distracted consumers during Black Friday and holiday seasons, costing Americans billions annually.

The AI-powered IRMO M1 exoskeleton features four modes, including turbo, eco, training and rest for hiking, running, cycling and sports with eight-hour battery life.

OpenAI announced upgrades for its ChatGPT Images platform on Tuesday, saying the program can now make more precise edits and produce images more quickly.

Chrome for Android now turns web articles into AI-powered podcast conversations. Get hands-free browsing with Google Gemini's natural audio summaries.

LastPass faces $1.6 million fine from U.K. regulators after 2022 data breach exposed 1.6 million users. Password manager failed proper security controls.

Petco disclosed a data breach exposing customer Social Security numbers, financial account details, and driver's license information due to a software error.

Baseball teams can now analyze complete swing mechanics in normal training environments using Theia's markerless AI system that processes standard high-speed footage.

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

Personal Technology