Converting rootless XmlReader to XmlDocument in .NET

This article is likely obsolete and it remains here for historical purposes. You should check for a more up-to-date solution.

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("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();
	
	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

Armand Niculescu

Armand Niculescu

Senior Full-stack developer and graphic designer with over 25 years of experience, Armand took on many challenges, from coding to project management and marketing.

6 Responses

  1. 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.

  2. 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”.

  3. 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

  4. 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.

  5. 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

Comments are closed.