Otaining Data as XML from SQL Server

[Guest]

Hi guys 'n' girls,
I tried to obtain data from SQL Server by using the "FOR XML AUTO, ELEMENTS"
clause and executing the SQLCommand with the ExecuteXmlReader() method.

My queries will not return very much data, but the data which they return
may change often.

I have a class which manages all the database stuff and it should return an
XPathDocument. As this class has a constructor with an XmlReader parameter, I
thought this could work great, but the data returned has no root node, so I
have a problem, as the XPathDocument will only contain the first node.

Can anyone give me a solution on how to get the data into a XPathDocument
object? All connections to the database should be closed before I return the
XPathDocument object to the caller.

I hope this was somehow clear and someone can provide a solution.

Thanks a lot...

Florian

 

[Nicholas Paldino [.NET/C# MVP]]

Florian,

It seems like all of the modes you have (AUTO, EXPLICIT, PATH) will not
return true XML documents, but the elements themselves. If you want to
process these, I would recommend getting the results as a string (I believe
you can just call ExecuteScalar and it will return the string for you) and
then wrapping it in an element that can serve as the document root.

Hope this helps.