Convert query/stored procedure result set to XML

Question

I'm looking to take the result of a SQL Server stored procedure and transform it into XML in .NET/C#

What I'm interested in is what standard libraries there are in .NET to help with the specifics of generating XML and if there any external libraries or tricks that can help automating the transformation from result set to XML.

I've looked at the XML options within SQL Server (05) in the past but they are not powerful enough for what I require.

Answer 1

There's basically two technologies out of the box in .NET that will allow you to create XML. In both cases, you won't get around writing quite a bit of code.

1) The XmlDocument approach, e.g. the XML DOM based way of doing things. You create a XmlDocument, create nodes, set attributes, create child nodes and so forth, and save all to disk in the end.

Pros: works on .NET 1.x and up, is quite widespread and well-known Cons: is a bit "clunky", keeps you whole XML structure in memory

See more info in the MSDN docs and countless articles and blog posts on the web

2) Then there's the newer Linq-to-XML approach, where you create your document using Linq statements. This is available in .NET 3.5 and up only, and some folks love it, other hate it with a lot of passion :-)

Pros: if you like LINQ, it feels quite natural and more "direct" than the XML DOM approach Cons: only on .NET 3.5 and up

See some articles and blog posts on the topic:

• http://www.codeguru.com/csharp/csharp/cs%5Flinq/article.php/c13715/
• http://blog.colinmackay.net/archive/2008/04/08/2194.aspx
• http://www.codeproject.com/KB/linq/Introduction%5FLINQToXML.aspx

Certainly lots more out there - just bing or google for "linq to xml".

Answer 2

Definitelly use System.Xml.Linq (XDocument, XElement, etc) from .NET 3.5 It's superior and easier to use. No need for an external library (for the most part)

Answer 3

Hi... I have to collect complete stored procedure in string format with complete values of

parameters suppose my stored procedure is

BEGIN UPDATE [XYZ].[dbo].[BillRegistration] SET [BillStatus] = @BillStatus ,[PassingDate] = @PassingDate WHERE [BillId]=@BillId and [YearPrefix]=@YearPrefix END

now i have collect all parameters and datafields values in string

I want outpur like this... in string

string strTemp = UPDATE [XYZ].[dbo].[BillRegistration] SET [BillStatus] = 'Y' ,[PassingDate] ='12/12/2009' WHERE [BillId]=@BillId and [YearPrefix]='2009'

If any one can help me please let m know as soon as possible.