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(&quot;Connection unavailable&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

7 replies

Comments are closed.