Hi. I know how to load a GridView from an xml file that already has data in it. How do you load data from a stored procedure into the xml file?

Thanks

 Do you mean how do you call a stored procedure and receive xml?

You can use the "for xml auto" clause

 
http://msdn.microsoft.com/en-us/library/ms345137.aspx
 

Thank you! That's really cool. The only thing is... how do I use it to populate my xml file in my application?

For xml auto returns the entire record as one big string.  You just need to use the LoadXml property of the XmlDocument class and pass the string as an argument like

objXmlDocument.LoadXml(databaseScalarResult);

How do you include the following in the query?

<?xml version="1.0" standalone="yes"?>

Do you mean how do you put that at the beginning of the xml document?  A very simple way would be just to concatenate it before you pass the string to the LoadXml method.

 You don't need to use the replace method.  the backslashes are just escape characters.  The \" signifies that you want a quote within your string, otherwise the compiler would think the quote means the string has ended.  Are you certain that

"<?xml version=\"1.0\" standalone=\"yes\"?><db><dbname name=\"ReportServer\"/><dbname name=\"ReportServerTempDB\"/><dbname name=\"Reporting\"/></db>"

is the value of NamesDs?  Because it compiles and runs just fine when I tried it.  On what line is the error occurring?

I set a break point on the following line: doc.Save(writer);
and I'm getting the following value for NamesDs: "<?xml version=\"1.0\" standalone=\"yes\"?><db><dbname name=\"ReportServer\"/><dbname name=\"ReportServerTempDB\"/><dbname name=\"Reporting\"/></db>"

I'm not getting the escape keys when I run the sproc in SQL Server.
SELECT custname as "@name"
FROM CustomerNames
FOR XML PATH('custname'), ROOT('cust');
I'm not getting an error anymore. But it's not writing into my xml file. I've tried the following 3 options with my xml file but it doesn't ever get written into when running the app. I'm sorry. I'm new to xml.

option 1: (blank)
option 2:
<?xml version="1.0" standalone="yes"?>
option 3:
<?xml version="1.0" standalone="yes"?>
<cust>
<custname name="" />
</cust>

Here's the GetXml method:

        public DataSet GetXml()
        {
            DataSet ds = new DataSet();
            SqlConnection con = new SqlConnection(strConn);
            con.Open();

            SqlCommand sc = new SqlCommand();
            sc.Connection = con;
            sc.CommandType = CommandType.Text;
            sc.CommandText = "dbo.GetCustNames";

            SqlDataAdapter myReader = new SqlDataAdapter(sc);
            myReader.Fill(ds);
            ds.WriteXml(HttpContext.Current.Server.MapPath("NamesXml.xml"));
            con.Close();
            con = null;

            return ds;
        }

myDAL = new DALClass();
string NamesDs = myDAL.GetXml(); //returns xml resultset
NamesDs = "<?xml version=\"1.0\" standalone=\"yes\"?>" + NamesDs;
NamesDs = NamesDs.Replace("\\", string.Empty);
XmlDocument doc = new XmlDocument();
doc.LoadXml(NamesDs);
XmlTextWriter writer = new XmlTextWriter("NamesXml.xml", null);
writer.Formatting = Formatting.Indented;
doc.Save(writer);

Hi MJ20 ,

Try my sample,

            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True");
            SqlCommand com = new SqlCommand("sqlxmltest",con);
            com.CommandType = CommandType.StoredProcedure;
            con.Open();
            object obj = com.ExecuteScalar();
            con.Close();

            XmlDocument doc = new XmlDocument();
            doc.LoadXml(obj.ToString ());
            doc.Save(Server.MapPath("sqlxmltest.xml"));

ALTER PROCEDURE dbo.sqlxmltest

AS
	SELECT id, name
FROM country FOR XML path('custname'), root('root')

Hi MJ20 ,

Just a remind.

MJ20:

XmlTextWriter writer = new XmlTextWriter("NamesXml.xml", null);
writer.Formatting = Formatting.Indented;
doc.Save(writer);

Please flush the writer . Try to invoke it's Flush method or Close method.

That's it, thank you!!!!

Is it possible to return multiple result sets using the FOR XML to fill an xml file in my app? It works fine when returning only one result set.
I've tried:
object obj = com.ExecuteNonQuery();
object obj = com.ExecuteReader();
object obj = com.ExecuteXmlReader();