|
| ||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
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" 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.
|
|
|
|
Back Up, Back Up, Back Up! This is not your typical lost data story. I was a good girl and I kept my files on the network drive just like the company recommended. The only thing I put on my hard drive was junk files - things that wouldn't bother me if the hard drive crashed. But not on the network drive. The drives are backed up and even my PC has a local back up program. Can you see where this is going? Is Desktop Search Deserving? Google, in their march to stay ahead of the pack of competition, has released Google Desktop Search. With Microsoft's MSN in the lead position and nipping at Google's heels, and Google's new shareholders to please, Google has more incentive than ever to deploy technology both better than the other guy's, and sooner. After all, the first to get loaded to the desktop is more likely to build and retain user loyalty. Smart Apple iPod Tips and Techniques Are you thinking of buying an Apple iPod? Or have you bought one? Why Build Your Own Gaming Computer? The best way to get the gaming computer that you want, that will provide optimum performance is to build your own computer. If you think you do not have the technical knowledge or are not capable of building a computer you are wrong. I'm pretty certain that I could teach my mother (who has a hard time working a VCR) how to build a computer. If you can plug a sweeper into a wall outlet and use a screw driver, you too can build a computer. How To Safeguard Your Computer? The following practice, if done regularly, may help you to safeguard your computer to some extent. 10 Tips to SPEED Up Your PC 1. Let your PC boot up completely before opening any applications. Wordpress Plugins Maybe you always wanted a feature that hasn't been available in the latest release of Wordpress. What you can do is either install a 3rd party plugin or write your own custom code to extend the features of Wordpress. iPod users get the picture iPod users start to get the picture and it's turning out to be colorful fun!! Windows Task Manager in English You use Ctrl+Alt+Del to see what's running on your PC, to close crashed programs and processes, and to check performance. You probably avoid a few processes whose names mean nothing to you, but they're essential to Windows. svchost.exe sure likes to appear all the time and multiple times at that. What's taskmgr.exe? Oh yeah, it's the window you're looking at right now. ctfmon? Is he related to Pokemon? navapsvc.exe? Navy? Napa Valley? Navel? NOTA (none of the above). Taking Advantage of the iPod Experience Music lovers have been carrying around radios and other bulky music devices in order to take their tunes along with them as they go from place to place. With the iPod ? a portable unit that's manufactured by Apple ? the music experience offers a higher quality in a much more compact size. Compare MP3 Players for the Coolest Features and Sweetest Deals How can you compare mp3 players to be sure you're getting the best features and price? You've checked some mp3 player ratings, and they're helpful. But iPod reviews and ratings don't answer all your questions. So how do you decide? Printing Multiple Copies of Photos I do a holiday letter every year and send them to friends so they know what's happening with my family and I ask them about theirs. Though addressing the envelopes and such is a pain, I enjoy doing the letter and adding a wallet-sized photo of my kids to show them off. To print nine wallets on a page, you don't have to copy the picture eight times in Word and then print it. Here's an easier way. Favorite Things To Do With Home Technology Save money. Save 10-15% on utility bills per month. Work with products that have a good value now, but also save money throughout the ownership of the home. Get energy savings through lighting management, irrigation control, solar energy, and temperature control. Lower monthly bills-how can you say no to that? 5 Minute Guide to Video Editing for Beginners Getting started with video editing is very simple you only need a few items and you can be producing quality movies at home that you will be able to enjoy for many years to come. Maybe even produce a hit Hollywood movie. Nephrology and Dialysis For a PDA Saving Lives With A Pocket PC Cisco Certification: A Survival Guide To The Cisco Cable Jungle One of the most confusing parts of beginning your Cisco studies is keeping all the cable types separate in your mind, and then remembering what they're used for. This often occurs when a CCNA or CCNP candidate starts putting together their own home practice lab, and they suddenly realize that they have the equipment to run labs, but not the cables. Computer Consulting 101 PC Troubleshooting Advice While most small businesses really do need to find a good local computer consulting business to take care of their computer problems, there are some computer problems that are simple enough for even a technophobe to handle. At Computer Consulting 101, we've found there's one very simple piece of advice that often doesn't get communicated to non-technical small business end users: When in doubt, reboot first. Your Home is in Your Hands I would like to tell you about a magic home. You or your children have bought a home and fixed it up very nicely. The parquet floors shine brightly, beautiful crystal chandeliers create a special atmosphere inside while fancy landscaping, decorative fountains and waterfalls create an atmosphere outside. Garage door opens to let you out to work in the morning. There is nobody home. Everybody is gone for the day. The house is empty. But you forgot to turn off the light or close the garage door. Turning around and coming back home is not an option... Tips For Getting Technical Support Help Online Sometimes your PC will start acting strange for no apparent reason or a program which you use regularly will all of a sudden stop working as it should. This can be a frustrating experience especially if you have no idea where to go for help. If this happens to you, don't panic! Here are some simple tips which may turn your frown upside down. The Many Benefits of Owning a DVR Has This Ever Happened To You? OK.. it's Friday night, the kids are asleep, and a movie you've been waiting for is on Pay-Per-View for the first time. You buy the movie, grab a bag of popcorn, get all comfy on the couch, and ten minutes into the movie "RINGGGG" it's the _______ phone!!! (you fill in the blank).
|
|
