title: SQL to XMLDocument
body: <p>I am working in Visual Studio 2013 within a console application and I am trying to convert SQL in a DataSet to XMLDocument. This is what I have so far from my XML output:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;ActvtyData schemaVersion="1.0" xmlns="ActivityData"&gt;
  &lt;Accts xmlns=""&gt;
   &lt;Acct&gt;
    &lt;AcctID&gt;123&lt;/AcctID&gt;
    &lt;Actns&gt;
      &lt;Actn&gt;
        &lt;Tp&gt;UNA&lt;/Tp&gt;
        &lt;Actvts&gt;
          &lt;Actvty&gt;
            &lt;Cd&gt;LOA&lt;/Cd&gt;
          &lt;/Actvty&gt;
        &lt;/Actvts&gt;
      &lt;/Actn&gt;
    &lt;/Actns&gt;
    &lt;Actns&gt;
      &lt;Actn&gt;
        &lt;Tp&gt;UNA&lt;/Tp&gt;
        &lt;Actvts&gt;
          &lt;Actvty&gt;
            &lt;Cd&gt;MLP&lt;/Cd&gt;
          &lt;/Actvty&gt;
        &lt;/Actvts&gt;
      &lt;/Actn&gt;
    &lt;/Actns&gt;
    &lt;AcctID&gt;12345&lt;/AcctID&gt;
    &lt;Actns&gt;
      &lt;Actn&gt;
        &lt;Tp&gt;UNA&lt;/Tp&gt;
        &lt;Actvts&gt;
          &lt;Actvty&gt;
            &lt;Cd&gt;LOA&lt;/Cd&gt;
          &lt;/Actvty&gt;
        &lt;/Actvts&gt;
      &lt;/Actn&gt;
    &lt;/Actns&gt;
   &lt;/Acct&gt;
  &lt;/Accts&gt;
  &lt;/ActvtyData&gt;
  </code></pre>
  
  <p>This is what I would like the output to be like:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;ActvtyData schemaVersion="1.0" xmlns="ActivityData"&gt;
    &lt;Accts xmlns=""&gt;
  &lt;Acct&gt;
    &lt;AcctID&gt;123&lt;/AcctID&gt;
    &lt;Actns&gt;
      &lt;Actn&gt;
        &lt;Tp&gt;UNA&lt;/Tp&gt;
        &lt;Actvts&gt;
          &lt;Actvty&gt;
            &lt;Cd&gt;LOA&lt;/Cd&gt;
          &lt;/Actvty&gt;
          &lt;Actvty&gt;
            &lt;Cd&gt;MLP&lt;/Cd&gt;
          &lt;/Actvty&gt;
        &lt;/Actvts&gt;
      &lt;/Actn&gt;
    &lt;/Actns&gt;
  &lt;/Acct&gt;
  &lt;Acct&gt;
    &lt;AcctID&gt;12345&lt;/AcctID&gt;
    &lt;Actns&gt;
      &lt;Actn&gt;
        &lt;Tp&gt;UNA&lt;/Tp&gt;
        &lt;Actvts&gt;
          &lt;Actvty&gt;
            &lt;Cd&gt;LOA&lt;/Cd&gt;
          &lt;/Actvty&gt;
        &lt;/Actvts&gt;
      &lt;/Actn&gt;
     &lt;/Actns&gt;
    &lt;/Acct&gt;
   &lt;/Accts&gt;
  &lt;/ActvtyData&gt;
  </code></pre>
  
  <p>This is my Method. I know its not the most efficient bit of code but it should work this way for now and its my first time doing XML and I need it to be done using XMLDocument:</p>
  
  <pre><code>     public static void CreateXML(DataSet ds)
       {
           int RecordTracker; //track current dataset record
           XmlNode accountNode = null;
  
           RecordTracker = 0;
  
           try
           {
               Boolean EndOfData;
               EndOfData = false;
               XmlDocument XML;
               XML = new XmlDocument();
               XmlNode XMLnode;
               XMLnode = XML.CreateXmlDeclaration("1.0", "UTF-8", null);
               XML.AppendChild(XMLnode);
  
               //Create the header elements and attributes
               XmlNode ActvtyDataNode = XML.CreateElement("ActvtyData");
               XmlAttribute ActvtyDataAttribute = XML.CreateAttribute("schemaVersion");
               ActvtyDataAttribute.Value = "1.0";
               ActvtyDataNode.Attributes.Append(ActvtyDataAttribute);
               ActvtyDataAttribute = XML.CreateAttribute("xmlns");
               ActvtyDataAttribute.Value = "ActivityData";
               ActvtyDataNode.Attributes.Append(ActvtyDataAttribute);
               XML.AppendChild(ActvtyDataNode);
  
               //Create the account elements and attributes (Accts xmlns="")
               XmlNode AcctsxmlnsNode = XML.CreateElement("Accts");
               XmlAttribute AcctsxmlnsAttribute = XML.CreateAttribute("xmlns");
               AcctsxmlnsNode.Attributes.Append(AcctsxmlnsAttribute);
               ActvtyDataNode.AppendChild(AcctsxmlnsNode);
  
               //Create the individual account element (Acct)
               XmlNode AcctxmlnsNode = XML.CreateElement("Acct");
               AcctsxmlnsNode.AppendChild(AcctxmlnsNode);
  
               //Set count to 0 sop it stops then there are no more accounts to cycle through
               //int intCountAccounts = 0;
               string strAccountNo = "";
               string strActionCodes = "";
               string strActvtyCodes = "";
               string strIAndECodes = "";
  
               //int intCurrentRow = 0;
               //No. of times to perform the loop
  
               accountNode = XML.CreateElement("AcctID");
  
               /// Main Account ID loop
               for (int intCountAccounts = 0; intCountAccounts &lt; ds.Tables[0].Rows.Count; intCountAccounts++)
               {
  
                   if (strAccountNo != ds.Tables[0].Rows[RecordTracker]["AcctID"].ToString())
                   {
                       accountNode = XML.CreateElement("AcctID");
                       accountNode.InnerText = ds.Tables[0].Rows[RecordTracker]["AcctID"].ToString();
                       //accountNode.AppendChild(XML.CreateTextNode(ds.Tables[0].Rows[intCountAccounts]["AcctID"].ToString()));
                       AcctxmlnsNode.AppendChild(accountNode);                  
  
                       strAccountNo = ds.Tables[0].Rows[RecordTracker]["AcctID"].ToString();
                   }                 
  
  
                       //Action codes
                       strActionCodes = "";
                       //Activity codes
                       strActvtyCodes = "";
                       strIAndECodes = "";
                       while (strAccountNo == ds.Tables[0].Rows[RecordTracker]["AcctID"].ToString())
                       {
                           XmlElement ActnsNode = XML.CreateElement("Actns");
                           XmlElement ActnNode = XML.CreateElement("Actn");
  
  
  
  
                           if (strActionCodes != ds.Tables[0].Rows[RecordTracker]["Tp"].ToString())
                           {
                               //Create Actns element
  
                               AcctxmlnsNode.AppendChild(ActnsNode);
  
                               //Create Actn element
                               ActnsNode.AppendChild(ActnNode);
  
  
  
  
                               XmlNode actnNode = XML.CreateElement("Tp");
  
                               actnNode.InnerText = ds.Tables[0].Rows[RecordTracker]["Tp"].ToString();
                               ActnNode.AppendChild(actnNode);
  
                               ////Check until match is found then move onto next
                               strActionCodes = ds.Tables[0].Rows[RecordTracker]["Tp"].ToString();
  
                           }
  
                           XmlElement ActvtyNode = XML.CreateElement("Actvty");
                           XmlElement ActvtsNode = XML.CreateElement("Actvts");
                           //Loop through for all activities that do not match
                           if (strActvtyCodes != ds.Tables[0].Rows[RecordTracker]["Cd"].ToString())
                           {
  
  
                                   ActnNode.AppendChild(ActvtsNode);
  
                                   ActvtsNode.AppendChild(ActvtyNode);
  
  
                                   XmlNode actvtyNode = XML.CreateElement("Cd");
                                   actvtyNode.InnerText = ds.Tables[0].Rows[RecordTracker]["Cd"].ToString();
                                   ActvtyNode.AppendChild(actvtyNode);
  
                                   ////Check until match is found then move onto next
                                   strActvtyCodes = ds.Tables[0].Rows[RecordTracker]["Cd"].ToString();
                               }
  
                               //increment to next record in data set
                               RecordTracker++;
                               if (RecordTracker &gt; intCountAccounts)
                               { break; }
                           }
  
  
  
  
  
  
  
                   }
               //Save as XML to file location
               XML.Save(@"C:\Users\JMERRY\ActivityFile.xml");
               }
  
  
           catch (Exception e)
           {
               Console.WriteLine(e.Message);
  
               //Environment.Exit(0);
               Console.ReadLine();
           }
      }
  </code></pre>