Lee Kelleher’s Weblog

I was in the middle of developing a member look-up AJAX function for an Umbraco project, when I ran into a slight problem, (confusion rather), about how to pull the XML back from SQL Server and return it to the browser (AJAX).

The SQL statement was straight-forward, very simple, does a LIKE query against the members table, no problem there. Added “FOR XML AUTO” to return the result-set back as an XML data-type … all going well so far.

Umbraco makes use of Microsoft Data Access Application Block‘s SqlHelper class, so I followed the same pattern.

XmlReader reader = SqlHelper.ExecuteXmlReader(connection, CommandType.Text, "SELECT n.id, n.text, m.Email, m.LoginName FROM cmsMember AS m INNER JOIN umbracoNode AS n ON m.nodeId = n.id WHERE n.text LIKE '%' + @query + '%' FOR XML AUTO", new SqlParameter[] { new SqlParameter("@query", query) })

At first I tried to return the XML as a String by calling XmlReader‘s GetOuterXml() method. But it returned nothing. After a lot of googling, (of converting an XmlReader to a String), I found a suggestion of iterating through the XmlReader, appending the current node to a StringBuilder.

Here’s what I ended up with…

using (SqlConnection connection = new SqlConnection(umbraco.GlobalSettings.DbDSN))
{
	using (XmlReader reader = SqlHelper.ExecuteXmlReader(connection, CommandType.Text, "SELECT n.id, n.text, m.Email, m.LoginName FROM cmsMember AS m INNER JOIN umbracoNode AS n ON m.nodeId = n.id WHERE n.text LIKE '%' + @query + '%' FOR XML AUTO", new SqlParameter[] { new SqlParameter("@query", query) }))
	{
		if (reader != null)
		{
			StringBuilder sb = new StringBuilder();

			while (reader.Read())
				sb.AppendLine(reader.ReadOuterXml());

			return sb.ToString();
		}
	}
}

return String.Empty;

I hope it helps… any improvements and suggestions are welcome!