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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.