8 replies to this topic

[LoneCoder]

I am using ASP.Net and SQL Server 2008. I need to create a XML file which looks like:

<cat>
   <name> "Categories"</name>
   <link> "link"</link>
</cat>

The Categories and link values should be added from the database.
I have tried everything but I cannot get it to work.
DO I have to create a XML file in ASP to do this?
These values are in the same table but there are other columns in the table as well.
My code looks something like this:

SqlCommand sqlcmd = new SqlCommand();
		//sqlcmd.CommandText = "Select * from Categories";
		DataSet ds = new DataSet();

		System.Data.SqlClient.SqlDataAdapter da = new	   System.Data.SqlClient.SqlDataAdapter("Select * from Categories", "Data Source=something;Initial Catalog=My Database;Integrated Security=True");
		da.Fill(ds);

	  int rows;
	  rows = ds.Tables[0].Rows.Count;

	  int i;

	  for (i = 0; i <= rows - 1; i++)
	  {
		  string Categories = ds.Tables[0].Rows[i].ItemArray[0].ToString();
		  string address = "[url="https://www.spendless.net.au/%22;"]https://example.com/";[/url]
		  string link = address + ds.Tables[0].Rows[i].ItemArray[1].ToString();
	  }

	 // ds.WriteXml(@"c:\output.xml", XmlWriteMode.WriteSchema);

I should create a XML filed based on these requirements and display it on another page.
I also need to add a header to the top of the XMLfile like

<name = some name date = today's date time = current time>

How can I do this??

Can someone please give me a detailed solution to this problem ASAP.
Thank you

Edited by Roger, 18 September 2012 - 07:38 AM.
removed formatting, added code tags

[kernelcoder]

I think 'da.fill(ds)' will not work as you need customized output. Besides that you are doing right. So, the logic is: iterate over the rows and write each node in XML. Now all you need to know is how to write an XML file of your own programatically. Here is a good tutorial on it.

[LoneCoder]

thank you.yes Fill gives the whole table as XML. I only require the specific output

[LoneCoder]

If I remove da = fill(ds) then how do I get the row count for the table. I need the row count for the iteration.

[kernelcoder]

If I remove da = fill(ds) then how do I get the row count for the table. I need the row count for the iteration.

Well, you'll use DataSet to fill the data by adapter and use the data from the tables in the DataSet but you should not use DataSet to write the XML file (e.g. ds.WriteXml), instead write the XML file in the way of your own (using XMLWritter as the tutorial shows or XmlTextWritter or XmlDocument).

[LoneCoder]

you mean to say something like this?

int i;
xmlwr.WriteStartElement("Cat");
for (i = 0; i <= rows - 1; i++)
{
xmlwr.WriteStartElement("cat");
xmlwr.xmlwr.WriteString(ds.Tables[0].Rows[i].ItemArray[0].ToString());
xmlwr.WriteEndElement;

xmlwr.WriteStartElement("link");
xmlwr.xmlwr.WriteString(ds.Tables[0].Rows[i].ItemArray[1].ToString());
xmlwr.WriteEndElement;

}

even if I do this the table does not giveme the desired data.

and what about if I want some text tobe displayed on my XML like
<title = "sometitle" date= "today's date" time = "curent time">

[kernelcoder]

you mean to say something like this?

int i;
xmlwr.WriteStartElement("Cat");
for (i = 0; i <= rows - 1; i++)
{
xmlwr.WriteStartElement("cat");
xmlwr.xmlwr.WriteString(ds.Tables[0].Rows[i].ItemArray[0].ToString());
xmlwr.WriteEndElement;

xmlwr.WriteStartElement("link");
xmlwr.xmlwr.WriteString(ds.Tables[0].Rows[i].ItemArray[1].ToString());
xmlwr.WriteEndElement;

}

Yeah, something like that. Please add code in the code tag from next time.

even if I do this the table does not giveme the desired data.

Why the table does not give you the desired data? Please explain a bit more.

and what about if I want some text tobe displayed on my XML like
<title = "sometitle" date= "today's date" time = "curent time">

You can write anything as you desire.

[LoneCoder]

Sorry about the code tag. I'm new to the site so I didn't know.

Well about the data. I want my first field "cat" to be the same as in the database.
But I want to append a string with the "link" value and save it.

such as link = "my name" + link;

How do I do this while saving??

Also I do not want the whole table to be saved as XML just the desired columns.
if I do not use da=fill(ds) how am I suppoed to get the row count from the database??

about the text to be displayed in the XML file what command do I use to display such text at the beginning of the XML doc.

Sorry I'm new to this!!

[kernelcoder]

using (XmlWriter writer = XmlWriter.Create("c:\\test.xml"))
{
    writer.WriteStartDocument();
    writer.WriteStartElement("Root");

    writer.WriteStartElement("Title");
    writer.WriteAttributeString("name", "some name");
    writer.WriteAttributeString("date", DateTime.Now.Date.ToString());
    writer.WriteAttributeString("time", DateTime.Now.TimeOfDay.ToString());
    writer.WriteEndElement(); // End Title

    writer.WriteStartElement("Categories");
    SqlCommand sqlcmd = new SqlCommand();
    DataSet ds = new DataSet();
    System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("Select * from Categories", "Data Source=something;Initial Catalog=My Database;Integrated Security=True");
    da.Fill(ds);

    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
        string category = ds.Tables[0].Rows[i].ItemArray[0].ToString();
        string address = "https://example.com/";
        string link = address + ds.Tables[0].Rows[i].ItemArray[1].ToString();
        writer.WriteStartElement("Cat");
        writer.WriteElementString("name", category);
        writer.WriteElementString("link", link);
        writer.WriteEndElement(); // end Cat
    }
    writer.WriteEndElement(); // end Categories

    writer.WriteEndElement();
    writer.WriteEndDocument();
}

I think the above code will just work for you.