After the release of the SQL Server 2000 the way the data is retrieved has taken a new dimension. The support for XML format is there from the version of SQL Server 2000.
With that it is possible to retrieve data from the SQL Server database in the XML format. SQLXML is used for this purpose. With such possibilities it is possible to access data in the SQL Server using URL based queries where the queries are transported using HTTP.
If you are already familiar with the SQL queries then it is very easy to create queries that return data in XML format. The syntax for such SQL queries would be,
SELECT … FOR XML mode
The values of the mode may be auto, explicit, raw, and nested. Depending on the mode that you are using the format of the XML output varies. If the mode is auto, the resulting XML document will have an element for each row that is found in the table of the SQL Server database.
In the explicit mode it is possible to define how the columns of the table should be returned to the query. In the raw mode all the fields of the table are considered as the attributes of the element of the XML data that is returned. The columns that have null values are not included. The returned elements have a row prefix to it.
The nested mode allows formatting to be done at the client side and it is the same as the auto mode except for this difference. The explicit mode is the most powerful mode for returning data. It is possible to define how you want the data and you can even use filters and sorts to get the data in the way you want.
Apart from these there are optional parameters for the SQL query. The optional parameters as the name indicates can either be used or neglected. The optional parameters that can be used are Binary Base64, Elements, and XMLData. With the optional parameters in place the syntax would take the form,
SELECT … FOR XML mode [, BINARY BASE64] [, ELEMENTS] [, XMLDATA]
The Binary Base64 option is used if you want to retrieve data in the binary format from the database. Binary data that is found in the database should be retrieved using this option. The modes that are used to retrieve data in binary format are the raw and the explicit modes.
The Elements option is used to return the data in the table as child elements. The fields of the row become the attributes of the element returned if you are not using the Elements option. Thus for each row you get an element with child elements being the fields of the row.
Auto mode is the only mode in which you can use the Elements option. If you want to define the format that is returned you need an XSD schema for that. The XMLData option allows this. This option adds a schema so that you get the format that you want for your XML data.
Managed classes are available in the .Net framework for interacting with the database. The Managed classes that are useful for this are SqlXmlCommand, SqlXmlParameter, SqlXmlAdapter, and SqlXmlException.
The SqlXmlCommand is used to query the XML templates. These are the XML documents that have the SQLXML queries in them. Processing the queries on the client side is possible with this managed class.
A managed class called the SqlXmlAdapter class is available which can be used to fill the dataset. This is the adapter class for the provider.
Another managed class called the SqlXmlParameter is available which can be used to pass parameters. This class is used along with the SqlXmlCommand managed class.
A class for trapping errors from the SQL Server is available which is called the SqlXmlException managed class.
A simple example of using the managed classes for getting XML output from the SQL Server database is given below. This code uses the XML AUTO mode for generating the XML data.
static string xmlstr = “Provider=SQLOLEDB;Server=(local);database=Orders;”;
public static void SampleSqlXml ()
{
Stream objStr = Console.OpenStandardOutput();
SqlXmlCommand sqlcmd = new SqlXmlCommand(xmlstr);
sqlcmd.Root = “Orders”;
sqlcmd.CommandType = SqlXmlCommandType.Sql;
sqlcmd.CommandText = “SELECT OrderNo, OrderValue FROM Orders FOR XML AUTO”;
strm = sqlcmd.ExecuteToStream(objStr);
objStr.Close();
}
For more information and samples on the managed classes that are used for retrieving data in XML format from SQL Server you can refer to the MSDN documentation.
Since we know that the results of the query are in the form of XML, it is possible to write your own ASP or ASP.Net code to display the values retrieved in XML format in the webpage. You can use the XSL for formatting the output in the way you want in the webpage. This allows the user to see the values in a particular format using the browser of their choice.
Author: Balaji B
Article Source: EzineArticles.com
Provided by: Programmable pressure cooker
73 Comments
I’m just done reading your write-up and I fairly enjoyed it. How to Retrieve Data from SQL Server Database in XML Format? | Learn XML is good! I had some doubts in the beginning of the submit though, but I held on reading and I’m happy I did. Good amusing submit, website owner! Continue posting and I’ll certainly be back again soon. Bless you and best regards.
I dig that orientation profoundly by far such that by far fabulous info above all .
I am contented to be one of the visitors above all on that great World Wide Web orientation (:, respect it in behalf of putting up.
I love the dear information you provide in the articles. I’ll bookmark your website and look again here frequently. We are quite certain I’ll learn lots of new stuff right here! All the best for the!
That was kind of inspiring! Completely surprising. Now I do know what I am heading to perform tomorrow
I simply couldn’t go away your website before suggesting that I actually loved the standard information an individual supply on your guests? Is going to be again incessantly to inspect new posts
A person necessarily help to make seriously posts I’d state. This is the very first time I frequented your web page and to this point? I surprised with the research you made to create this actual put up incredible. Great job!
I’ve said that least 3978070 times. The problem this like that is they are just too compilcated for the average bird, if you know what I mean
It¡¯s actually a great and useful piece of info. I¡¯m glad that you just shared this helpful info with us. Please keep us up to date like this. Thanks for sharing.
Hey all, I was just checking out this blog and I really appreciate the basis of the post, teriffic work.
The vacation delivers on offer are : believed a selection of some with the most selected and additionally budget-friendly global. Any of these lodgings tend to be really used along units could accented by indicates of pretty shoreline supplying crystal-clear turbulent waters, concurrent with the Ocean. hotels packages
Extremely rated post. I study something completely new on different blogs everyday. Deciding on one . stimulating to read the paper content from other writers and study slightly one thing from their website. I’d like to apply sure of this content on my weblog you’re mind. Natually I’ll give a link right here we are at your web-site. Recognize your sharing.
Oh my goodness! a tremendous article dude. Thanks However I am experiencing challenge with ur rss . Don’t know why Unable to subscribe to it. Is there anyone getting identical rss problem? Anyone who knows kindly respond. Thnkx
Hola! I’ve been following your weblog for a while now and finally got the bravery to go ahead and give you a shout out from Kingwood Texas! Just wanted to say keep up the fantastic work!
Just wish to say your article is as surprising. The clearness in your put up is simply excellent and that i can think you are an expert in this subject. Fine along with your permission allow me to grab your feed to stay up to date with approaching post. Thank you a million and please continue the gratifying work.
Hey! Someone in my Facebook group shared this site with us so I came to look it over. I’m definitely enjoying the information. I’m bookmarking and will be tweeting this to my followers! Terrific blog and terrific style and design.
I’ve to admit that i typically get bored to learn the entire thing but i feel you’ll be able to add some value. Bravo !
Very effectively written article. Will probably be precious to anyone who uses it, including myself. Keep up the nice work! Iwill be back to take a look at your posts again soon.
I was wondering if you ever thought of changing the layout of your blog? Its very well written; I love what youve got to say. But maybe you could a little more in the way of content so people could connect with it better. Youve got an awful lot of text for only having 1 or two pictures. Maybe you could space it out better?
Thanks for the auspicious writeup. It actually was once a leisure account it. Look advanced to far added agreeable from you! However, how could we be in contact?
I have been absent for a while, but now I remember why I used to love this web site. Thank you, I’ll try and check back more frequently. How frequently you update your web site?
It’s really a great and useful piece of info. I am glad that you shared this helpful info with us. Please keep us informed like this. Thanks for sharing.
But wanna comment on couple of common issues, The website style is perfect, the content material is truly very good : D.
I was suggested this blog by my cousin. I am not sure whether this post is written by him as nobody else know such detailed about my problem. You’re incredible! Thanks!
I found your posting to be insightful! Thank you.
One other important part is that if you are an older person, travel insurance regarding pensioners is something you should make sure you really take into consideration. The more aged you are, the more at risk you’re for having something terrible happen to you while overseas. If you are not necessarily covered by a few comprehensive insurance cover, you could have a number of serious troubles. Thanks for discussing your good tips on this blog site.
elisha, that house has several rooms for rent. I just hope that the number is still good.
i like how people think on this blog
I wanted to thanks for this great learn!! I positively enjoying every little little bit of it I’ve you bookmarked to take a look at new stuff you put up
this text help me to return out a few ideas to help me make my very own agenda for my complete time web business. http://www.pitstop.getlisted.co.nz/how-much-is-a-wof
What’s up everybody, I am sure you will be enjoying here by watching these kinds of hilarious video lessons.
Lovely just what I was looking for.Thanks to the author for taking his clock time on this one.
I found your posting to be insightful! Thank you.
I’ve recently started a blog, the information you offer on this site has helped me tremendously. Thanks for all of your time & work.
My wife and i felt now glad Raymond could finish off his reports with the precious recommendations he acquired while using the site. It is now and again perplexing to just choose to be giving away tactics which often a number of people may have been making money from. So we remember we’ve got the writer to appreciate for this. All the explanations you’ve made, the simple site menu, the relationships you can help instill – it’s all wonderful, and it’s making our son in addition to the family reason why that situation is thrilling, and that’s particularly vital. Thank you for the whole lot!
F*ckin’ remarkable things here. I am very glad to see your article. Thanks a lot and i am looking forward to contact you. Will you kindly drop me a e-mail?
Touche. Outstanding arguments. Keep up the amazing spirit.
Just to let you know, this content appears a little bit weird from my smart phone. Who knows maybe it is just my mobile phone. Great article by the way.
One of my favorite posts.
If you might e-mail me with a couple of strategies on just the way you made your blog look this excellent, Id be grateful.
Hey there! I just wanted to ask if you ever have any trouble with hackers? My last blog (wordpress) was hacked and I ended up losing months of hard work due to no data backup. Do you have any methods to stop hackers?
I’ll right away grab your rss as I can not to find your e-mail subscription hyperlink or e-newsletter service. Do you have any? Kindly allow me recognise so that I may just subscribe. Thanks.
What¡¯s Going down i am new to this, I stumbled upon this I’ve found It absolutely useful and it has helped me out loads. I hope to give a contribution & help different customers like its aided me. Good job.
You have noted very interesting points ! ps nice website .
Zune and iPod: Most people compare the Zune to the Touch, but after seeing how slim and surprisingly small and light it is, I consider it to be a rather unique hybrid that combines qualities of both the Touch and the Nano. It’s very colorful and lovely OLED screen is slightly smaller than the touch screen, but the player itself feels quite a bit smaller and lighter. It weighs about 2/3 as much, and is noticeably smaller in width and height, while being just a hair thicker.
Would you be thinking about exchanging hyperlinks?
Thanks for spending the time to discuss this, I really feel strongly about it and love studying more on this topic. If doable, as you turn into an expert, would you mind updating your weblog with more details?
Simply wanna comment on few general things, The website pattern is perfect, the subject matter is rattling fantastic : D.
yea good Work
http://www.driversday.getlisted.co.nz/where-can-i-drive-a-race-car
Sehr guter Kommentar. Ich denke, dass jeder in dieser Weise zu handeln, und das ist am besten beschrieben!
I am experiencing a situation with your rss feed . Don’t know why I am not able to subscribe to it. Is there anybody getting equivalent rss problem? Anyone who knows kindly respond. Thanks
Good article , thanks and we want more! Added to FeedBurner as well
The guidelines you shared here are incredibly useful. It absolutely was such an exciting surprise to see that looking forward to me as i woke up now. They are continually to the point and easy to grasp. Warm regards for the clever ideas you’ve got shared above.
thanks !! quite helpful post!
I appreciate your wordpress web template, where did you get a hold of it from?
The flow of your writing is fantastic and so is your post. I had wanted to look over someone’s perspectives on this topic and yet I could not discover any good websites together with the to-the-point information which i was looking for. So, I am genuinely grateful to your site and especially to you. Make sure you, do keep your blogposts updated.
Hello, you used to write fantastic, but the last few posts have been kinda boring… I miss your great writings. Past few posts are just a little bit out of track! come on!
You made some decent factors there. I looked on the web for the issue and located most people will go along with along with your website.
Good tips. You didn’t write sponsoring contest. This provides you backlinks. http://www.myhuntingnews.com
This actually answered the problem, thank you!
Would you be involved in exchanging links?
Aw, this was a really nice post. In thought I want to write like this – taking time and precise effort to make an excellent article is very rare…
Hello, Neat post. There’s a problem together with your website in internet explorer, could test this… IE still is the market leader and a huge section of folks will pass over your wonderful writing due to this problem.
Witty! I’m bookmarking you site for future use.
That was clever. I’ll be stopping back.
This was a great post, thanks for the info.
There are certainly numerous particulars like that to take into consideration. That may be a nice point to deliver.
It’s hard to find knowledgeable people on this topic however you sound like you know what you’re talking about! Thanks
You should take part in a contest for one of the best blogs on the web. I will recommend this site!
I appreciate the insightful post. Thanks.
Hello there, just became aware of your blog through Google, and found that it is really informative. I am going to watch out for brussels. I will appreciate if you continue this in future. Numerous people will be benefited from your writing. Cheers!
That was clever. I’ll be stopping back.
I was wondering if you ever considered changing the layout of your blog? Its very well written; I love what youve got to say. But maybe you could a little more in the way of content so people could connect with it better. Youve got an awful lot of text for only having one or two images. Maybe you could space it out better?
Post a Comment