More SQL XML, Making It Easier...

Well, I've previously used stored procedures to execute SQLXML queries, pulling the XML out using a standard SqlDataAdapter and concatenate the returned rows via a StringBuilder.

There is, however, a different mechanism to use:

• Microsoft.Data.SqlXml.SqlXmlCommand:
  http://www.microsoft.com/downloads/details.aspx?familyid=51d4a154-8e23-47d2-a033-764259cfb53b&displaylang=en

Some concerns:

• It does throw a large number of exceptions, so your code needs to be clean.
• You will need to remove System.Data.SqlClient because the new DLL replaces the namespaces found in the original DLL.

Example code:

using Microsoft.Data.SqlXml;

string connectionString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

SqlXmlCommand queryCommand = new SqlXmlCommand(connectionString);

queryCommand.CommandText = @"SQL * FROM something FOR XML";

SqlXmlParameter queryParameter = queryCommand.CreateParameter();

queryParameter.Value = 2860;

queryCommand.RootTag = "root";

queryCommand.XslPath = Server.MapPath("~/XSLT/formatter.xslt");

queryCommand.ClientSideXml = true;

XmlDocument xmlChartData = new XmlDocument();

xmlChartData.Load(queryCommand.ExecuteXmlReader());