Using .NET in conjunction with SQLS' "FOR XML AUTO" option

If you want to manipulate raw XML data returned from SQLS with .NET, you'll need to use something like the following:

XmlDocument doc = new XmlDocument();
using (SqlConnection conn = new SqlConnection("...")) {
SqlCommand cmd = new SqlCommand(@"SELECT 1 FOR XML AUTO", conn);
conn.Open();
doc.Load(cmd.ExecuteXmlReader());
conn.Close();
}

You could use something similar in conjunction with a stored proc also, using either XML AUTO, RAW or EXPLICIT options.

CREATE PROCEDURE sp_GetInfo

AS

SELECT 123 AS Tag, 0 As Parent, p_name AS [Person!123!p_name], p_add AS [Person!123!p_add]
FROM TBL_People_Info
FOR XML EXPLICIT

GO

This will give you some nice raw xml data.

Then you can create some .NET code to fire off and return results from the stored proc.

private SqlCommand BuildQueryCommand(string storedProcName,
IDataParameter[] parameters)
{
SqlCommand command =
new SqlCommand(storedProcName, Connection);
command.CommandType = CommandType.StoredProcedure;

foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}

protected System.Xml.XmlDocument RunProcedure(string storedProcName, IDataParameter[] parameters)
{
System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();

SqlCommand cmd = BuildQueryCommand(storedProcName, parameters);
Connection.Open();

xDoc.Load(cmd.ExecuteXmlReader());
Connection.Close();
return xDoc;
}

You can then use these methods on your page, or within any higher business-level class.

System.Xml.XmlDocument xDoc = RunProcedure("sp_GetInfo", new IDataParameter[]{});

You're then free to do what you will with the data returned. Transform it, output it in raw form, etc.