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:

public static XmlDocument ExecuteSelectXmlCmd(string sQuery, bool nodes)
{
	if (sQuery.ToLower().IndexOf("for xml") < 0)
	{
		sQuery += " FOR XML AUTO";
		if (nodes == true)
		sQuery += ", ELEMENTS";
	}
 
	string sConn = ConfigurationManager.AppSettings["ConnectionString"] /* get the connection string from web.config */
	SqlConnection oConn = new SqlConnection(sConn);
	try
	{
		oConn.Open();
	}
	catch { throw new Exception(sConn); }
	if (oConn.State != ConnectionState.Open)
	throw new Exception("Connection unavailable");
 
	SqlCommand cmd = new SqlCommand();
 
	// this can be a security issue!
	cmd.CommandText = sQuery;
 
	cmd.Connection = oConn;
 
	cmd.CommandType = CommandType.Text;
	XmlReader xmlRead = cmd.ExecuteXmlReader();
	oConn.Close();
}

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.

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:

public static XmlDocument ExecuteSelectXmlCmd(string sQuery, string sRootName, bool nodes)
{
	if (sQuery.ToLower().IndexOf("for xml") < 0)
	{
		sQuery += " FOR XML AUTO";
		if (nodes == true)
		sQuery += ", ELEMENTS";
	}
	string sConn = ConfigurationManager.AppSettings["ConnectionString"] /* get the connection string from web.config */
	SqlConnection oConn = new SqlConnection(sConn);
	try
	{
		oConn.Open();
	}
	catch { throw new Exception(sConn); }
	if (oConn.State != ConnectionState.Open)
	throw new Exception(&amp;quot;Connection unavailable&amp;quot;);
 
	SqlCommand cmd = new SqlCommand();
	// this can be a security issue!
	cmd.CommandText = sQuery;
	cmd.Connection = oConn;
	cmd.CommandType = CommandType.Text;
	XmlReader xmlRead = cmd.ExecuteXmlReader();
 
	XPathDocument xp = new XPathDocument(xmlRead);
	XPathNavigator xn = xp.CreateNavigator();
	XmlDocument xd = new XmlDocument();
	XmlNode root = xd.CreateElement(sRootName);
	root.InnerXml = xn.OuterXml;
	xd.AppendChild(root);
	oConn.Close();
	return xd;
}

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:

string sConn = ConfigurationManager.AppSettings["ConnectionString"];
SqlXmlCommand cmd = new SqlXmlCommand(sConn);
cmd.RootTag = sRootName;
cmd.CommandText = sQuery;
XmlReader xmlRead = cmd.ExecuteXmlReader;
XmlDocument xd = new XmlDocument();
xd.Load(xmlRead);
oConn.Close();
return xd;

The rest stays the same.

Futher reading

Share and Enjoy:

7 responses to “Converting rootless XmlReader to XmlDocument in .NET”

J
February 12, 2007 at 17:29 | Permalink | Reply
To quote you “There’s no straightforward way to convert a XmlReader 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.”.
Isn’t much easier to use the “Load” method on the XmlDocument class? One of the 4 overloads accept an XmlReader as aparameter.
Armand Niculescu
February 15, 2007 at 12:16 | Permalink | Reply
There was some poor wording on my part.
It should have read “There’s no straightforward way to convert a XmlReader without a root node to a XmlDocument”.
sam sinfield
May 21, 2007 at 14:28 | Permalink | Reply
This looked to be exactly what I needed for converting data from an sql server db to a xmldoc. Unfortunately, I’m using .net 1.1 and don’t have the option to upgrade and this has only been added in .net 2.0. Is there any other, elegant way of doing this in 1.1 without having to sequentially read through the XmlReader
Armand Niculescu
May 21, 2007 at 17:48 | Permalink | Reply
Sam, if you’re using MSSQL 2005, you can specify the root node in the query itself, like this:
...FOR XML AUTO, ROOT('doc')
I don’t know of any other way.
Wayne Scholar
June 6, 2007 at 15:32 | Permalink | Reply
Great Article. We’re using Flex and needed this badly!
Thanks!
Wayne
Sebastian
December 7, 2008 at 07:46 | Permalink | Reply
Great Article. I wish I had find it sooner.
Here is another way of doing the same with an XMLDocumentFragment.
It works, but it could use some cleanup, specially around the ugly way I’m parsing each node.
Just go to my blog look at my post.
Thanks! Sebastian
xmlreader - StartTags.com
January 27, 2010 at 04:25 | Permalink
[...] @ StartTags Converting rootless XmlReader to XmlDocument in .NETHow to convert an XmlReader that lack the root node into a XmlDocumentXML Reader Flash panel | [...]