How to iterate all nodes?

Question

I am trying to produce the following output:

<article> <status> </status> ....</article>
<article> <status> </status> ....</article>

I need little assistance with the looping logic - any advice where i may be going wrong. I tried using the "for" loop but thats failing to produce the desired output. Please advise. Thank you.

public static string createArticleALL()
    {

        XElement xeRoot = new XElement("article");
        XDocument xDoc = new XDocument(xeRoot);

        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["###"].ConnectionString))

        {
            con.Open();

            using (SqlCommand command = new SqlCommand("####", con))
            {

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    string title = reader.GetString(0);  
                    string body = reader.GetString(4);

                    string pub = reader["publication_id"].ToString();
                    string iss = reader["issue_id"].ToString();
                    string sid = reader["STORYID"].ToString(); 


                    string c = url(title, pub, iss, sid);

        DateTime dt = DateTime.Today;

      foreach (XElement element in xDoc.Descendants("article"))
      {

        XElement xeStatus = new XElement("status", "Approved");
        xeRoot.Add(xeStatus);

        XElement xeTitle = new XElement("title", title);
        xeRoot.Add(xeTitle);

        XElement xeSubTitle = new XElement("subtitle", title);
        xeRoot.Add(xeSubTitle);

        XElement xeSynopsis = new XElement("synopsis", body + "...");
        xeRoot.Add(xeSynopsis);

        XElement xeURL = new XElement("url", c);
        xeRoot.Add(xeURL);

        XElement xeDisplayDate = new XElement("display_date", dt);
        xeRoot.Add(xeDisplayDate);


      }


                }
            }


        return xDoc.ToString();
        }
        return null;

    }

Answer 1

Cleaned this up. You have to create an article node, and add it to the document for each of the while loops. Assuming you're not using the title stuff elsewhere, I removed the extra bits.

XDocument xDoc = new XDocument(new XElement("Root"));
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["###"].ConnectionString))
{
    con.Open();

    using (SqlCommand command = new SqlCommand("####", con))
    {
        XElement article;
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            xDoc.Add(article = new XElement("article"));
            article.Add(new XElement("status", "Approved"));
            string title;
            article.Add(new XElement("title", title = reader.GetString(0)));
            article.Add( new XElement("subtitle", title));
            article.Add(new XElement("synopsis", reader.GetString(4) + "..."));

            string pub = reader["publication_id"].ToString();
            string iss = reader["issue_id"].ToString();
            string sid = reader["STORYID"].ToString(); 

            string c = url(title, pub, iss, sid);
            article.Add(new XElement("url", c));

            article.Add(new XElement("display_date", DateTime.Today));
        }
    }
}