Swetha Vijayan Swetha Vijayan - 2 months ago 12
C# Question

How to store the XML file returned from SQL Server during selection statement in C#

During fetching of data in C#, how can we store the XML file returned from a SQL Server stored procedure into a

string
or
var
?

My code:

SqlDataReader rdr = SqlHelper.ExecuteReader(Conn, CommandType.StoredProcedure, spName, ListParam.ToArray());


My query is

select *
from table_Name
FOR XML AUTO, ROOT ('Collection');

Answer

Edited as per the OP's request.

StringBuilder sb = new StringBuilder();
using (var reader = SqlHelper.ExecuteXmlReader(Conn, CommandType.StoredProcedure, spName, ListParam.ToArray()))
{
    if (reader == null) return;     
    while(reader.Read())
    {
        sb.AppendLine(reader.ReadOuterXml());
    }
    string xmlVal = sb.ToString(); // You can get the xml as string here. 
}
Comments