create xml from array for display

I have an array of data that is created from data returned from a sql stored proc that I want to display to a web browser as xml data.  I can get it to work if I only have 1 record in the array but when i have multiple records and loop through to build my array, I get an error message "Only one top level element is allowed in an XML document. "  Can't figure out where I went wrong.  Please help if you can.  Below is the code for the xml creation.

     Dim doc As New XmlDocument()

     ' XML declaration
     Dim declaration As XmlNode = doc.CreateNode(XmlNodeType.XmlDeclaration, Nothing, Nothing) doc.AppendChild(declaration)

         ' Root element
     Dim root As XmlElement = doc.CreateElement("drawSearchResults")doc.AppendChild(root)
     ' read results from sql server stored proc
     While myDataReader.Read
          ' Sub-element
          Dim drawNodeNum As XmlElement = doc.CreateElement("draw" & intCounter)            root.AppendChild(drawNodeNum)

          /*build and sort the array code*/

          ' Attribute: N1
          Dim Nmbr01 As XmlAttribute = doc.CreateAttribute("N1")
          Nmbr01.Value = arrNumbers(1)

          ' Attribute: N2
          Dim Nmbr02 As XmlAttribute = doc.CreateAttribute("N2")
          Nmbr02.Value = arrNumbers(2)
          ' Attribute: N3
          Dim Nmbr03 As XmlAttribute = doc.CreateAttribute("N3")
          Nmbr03.Value = arrNumbers(3)
          /*more attribute creation code*/
          intCounter += intCounter
     End While
Who is Participating?
Try this:

Dim xmlDoc  As XmlDocument = new XmlDocument()

// Write down the XML declaration
Dim xmlDeclaration  as XmlDeclaration = xmlDoc.CreateXmlDeclaration("1.0","utf-8",nothing)

// Create the root element
Dim rootNode   as XmlElement = xmlDoc.CreateElement("drawSearchResults")
xmlDoc.InsertBefore(xmlDeclaration, xmlDoc.DocumentElement)

' read results from sql server stored proc
While myDataReader.Read

   // Create a new <Category> element and add it to the root node
   Dim drawNodeNum as XmlElement = xmlDoc.CreateElement("draw" & intCounter)

   /*build and sort the array code*/

  // Set attribute name and value!
  drawNodeNum .SetAttribute("ID", "01")


End While


Open in new window

Note that I also moved the xmlDoc.Save outside the While.
XML is a text so you can build XML nodes just by using strings. It is not a best choice but can be useful.
Like (sorry for mix c# and vb.Net, hope you will catch the idea):
string rootXML = "<drawSearchResults>~rows</drawSearchResults>";
string drawTemplate = "<draw~in><N1>~n1</N1><N2>~n2</N2><N3>~n3</N3></draw~in>";
StringBuilder rows = new StringBuilder();
string tempRow = "";
int intCounter = 0; // or any other value you need
while (myDataReader.Read)
          // Sub-element
          tempRow = drawTemplate.Replace("~in", intCounter);

          /*build and sort the array code*/

         //Attribute: N1
         tempRow = tempRow.Replace("~n1", arrNumbers(1));

         //Attribute: N2
         tempRow = tempRow.Replace("~n2", arrNumbers(2));

         //Attribute: N3
         tempRow = tempRow.Replace("~n3", arrNumbers(3));
          /*more attribute creation code*/
          // intCounter += intCounter ???
rootXML.Replace("~rows", rows.ToString());

XmlDocument doc = new XmlDocument();

Open in new window

Here you check (for instance) doc.HasChildNodes
and then just work with doc.OuterXML
Other options that you have are to get the data as XML already from (assuming) SQL Server.

Alternatively, I often find that using the XmlTextWriter reads easier than using an XmlDocument.
 XmlTextWriter w = new XmlTextWriter(Console.Out);
 w.Formatting = Formatting.Indented;

Open in new window
lotterygirlAuthor Commented:
As suggested all I needed to do was move the             doc.Save(Response.Output) outside the while statement.

Surprising how such an easy thing can really trip you up.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.