Converting rootless XmlReader to XmlDocument in .NET

MS SQL has the ability to retrieve data as XML by using the "FOR XML [RAW | AUTO [ELEMENTS] | EXPLICIT]" parameters, which is very useful especially when used in a web service that communicates with a Flash / Flex client as the results don't have to be converted, but just output to the client.

Your code might look something like this:

C#:

Besides the possible security issues (it'd be much better to use parametrized queries), there's a problem with this code: it returns an XmlReader without a root node, so you can't return that to your client.

Follow up:

There's no straightforward way to convert a XmlReader that does not have a root element to a XmlDocument - most of the code samples I've seen simply read sequentially through the XmlReader, appending data to a string. Obviously this is inneficient from a performance standpoint.

The better way to handle this is via a rather obscure method, mentioned on MSDN:

It involves using an XPathNavigator, like this:

XPathDocument xp = new XPathDocument(xmlRead);
XPathNavigator xn = xp.CreateNavigator();

so you end up with an XmlNode.

Then you can create the XmlDocument like usual, with a root node

XmlDocument xd = new XmlDocument();
XmlNode root = xd.CreateElement("root");

and dump the node inside:

root.InnerXml = xn.OuterXml;
xd.AppendChild(root);

So the whole method might look like this:

C#:

SQLXML

If you don't want to add the root tag by yourself or if you need to apply a XSL transformation on the returned XML, you can use SQLXML. In case you don't have it installed, you can download it from Microsoft and install it; then you can add it as reference in your project.

To use it, add this in your class:

using Microsoft.Data.SqlXml;

then, modify the query part of the method like this:

C#:

The rest stays the same.

Futher reading

• Extract XML Data using XPathNavigator
• SQLXML 4.0 Programming
• Accessing SQLXML Functionality in the .NET Environment
• Using Parameterized Queries in ASP.NET