XML and XmlDataSource Control

Re: Load XML file From Sproc

MJ20 — Fri, 16 May 2008 00:40:03 GMT

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();

Re: Load XML file From Sproc

MJ20 — Thu, 15 May 2008 05:56:31 GMT

That's it, thank you!!!!

Re: Load XML file From Sproc

Samu Zhang - MSFT — Wed, 14 May 2008 08:38:36 GMT

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.

Re: Load XML file From Sproc

Samu Zhang - MSFT — Tue, 13 May 2008 07:20:58 GMT

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')

Re: Load XML file From Sproc

MJ20 — Sun, 11 May 2008 21:28:29 GMT

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);

Re: Load XML file From Sproc

MJ20 — Sun, 11 May 2008 01:14:47 GMT

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>

Re: Load XML file From Sproc

jamesqua — Sat, 10 May 2008 18:32:07 GMT

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?

Re: Load XML file From Sproc

MJ20 — Fri, 09 May 2008 17:55:15 GMT

The value of NamesDS is:
"<?xml version=\"1.0\" standalone=\"yes\"?><db><dbname name=\"ReportServer\"/><dbname name=\"ReportServerTempDB\"/><dbname name=\"Reporting\"/></db>"
even when I use the .Replace method below to try to remove the \
I'm getting the error Root element is missing.

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);

Re: Load XML file From Sproc

jamesqua — Fri, 09 May 2008 05:19:39 GMT

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.

Re: Load XML file From Sproc

MJ20 — Fri, 09 May 2008 02:23:36 GMT

How do you include the following in the query?

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

Re: Load XML file From Sproc

jamesqua — Fri, 09 May 2008 01:30:27 GMT

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);

Re: Load XML file From Sproc

MJ20 — Fri, 09 May 2008 00:58:50 GMT

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

Re: Load XML file From Sproc

jamesqua — Thu, 08 May 2008 21:54:00 GMT

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

Load XML file From Sproc

MJ20 — Thu, 08 May 2008 21:02:12 GMT

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