Geeks With Blogs
Sudheer Kumar ASP.Net, C#, BizTalk, MSBuild, WPF, WCF, WF....
We had a requirement to generate some extract data in the form of XML.
So while designing the solution, I had 2 options.
1. Use FOR XML in the Query with SQL 2005
2. Use regular SQL Queries and usign a Custom DataSet, serialize the data to XML.

SQL 2005 has nice options to get the XML.
You can assign name spaces to nodes, can have nested XMLs, can have custom Root and Child Xml node names.
One example can be found here.
http://www.wrox.com/WileyCDA/Section/id-301088.html

But FOR XML queries are much slower when getting huge amounts of data.
You can find a comparison here.
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1265579,00.html

So the bottom line is, FORXML is suitable only for small output.
Else do the logic in the business components.
And in most of the circumstances, the load on the database need to be kept low.
Hence second method was found the more suitable one. Posted on Monday, January 7, 2008 12:10 PM SQL/XML , SQL 2005 | Back to top


Comments on this post: FOR XML Performance with SQL 2005

# re: FOR XML Performance with SQL 2005
Requesting Gravatar...
The forxml also generates a fragment. I used it against 1 million rows on a conversion program. It took 1/2 hour. Good post.
Left by Ken on Jan 07, 2008 2:34 PM

# re: FOR XML Performance with SQL 2005
Requesting Gravatar...
Who can advise something about web development? It is a new issue for me and I want to be in the know as soon as possible.
Left by web development on Mar 25, 2011 3:45 AM

# re: FOR XML Performance with SQL 2005
Requesting Gravatar...
Good job.
Left by led watch on Mar 31, 2011 3:52 AM

# re: FOR XML Performance with SQL 2005
Requesting Gravatar...
is it that XML queries are much slower when getting huge amounts of data????thanks for you share,Vivi
Left by silicone wtches on Jul 11, 2012 8:50 PM

# re: FOR XML Performance with SQL 2005
Requesting Gravatar...
is it that XML queries are much slower when getting huge amounts of data????thanks for you share,Vivi
sorry,i forget to write on website..
Left by silicone watch on Jul 11, 2012 8:53 PM

Your comment:
 (will show your gravatar)


Copyright © Sudheer Kumar | Powered by: GeeksWithBlogs.net