Member Avatar for beckybc920

I am new to developing so please bear with me, I am using SQL Server to create an xml output (using the FOR XML clause). In Query analyzer the xml document is produced correctly with all the element and child nodes. I am using asp.net and the XmlReader to read the output and display it as an xml doc. When I do this all the parent nodes are not being displayed properly. I am not sure what to do to fix this or what i am doing wrong. Below is some of the code and an example of what is happening.

Any suggestions??

Dim reader As System.Xml.XmlReader = sqlCommand.ExecuteXmlReader()
        
        'reader.Read();
        Response.Write("<?xml version='1.0' encoding='utf-8' ?><root xmlns:sql='urn:schemas-microsoft-com:xml-sql'>")
        ' Response.Write("<output>")
        While reader.Read()
             
            Response.Write(reader.ReadOuterXml())
        End While
        ' Response.Write("</output>")
        Response.Write("</root>")

The query from SQL Server would be something like Select * from Customers FOR XML AUTO, ELEMENTS

The output in SQL Server would look like:

<customers>
  <fname>John</fname>
  <lname>Doe</lname>
 <address>1234 Somewhere St</address>
</customers>
<customers>
  <fname>Jane</fname>
  <lname>Doe</lname>
 <address>1234 Somewhere St</address>
</customers>
<customers>
  <fname>Suzy</fname>
  <lname>Q</lname>
 <address>1234 Outta Here Dr</address>
</customers>
<customers>
  <fname>No</fname>
  <lname>Name</lname>
 <address>6789 Nowhere St</address>
</customers>

After I execute the above XmlReader in asp.net it returns like this:

<customers>
  <fname>John</fname>
  <lname>Doe</lname>
 <address>1234 Somewhere St</address>
</customers>
<customers>
  <fname>Jane</fname>
  <lname>Doe</lname>
 <address>1234 Somewhere St</address>
</customers>
<fname>Suzy</fname>  Notice the <customers> node is not displaying for this element
<lname>Q</lname>
 <address>1234 Outta Here Dr</address>
<customers>
  <fname>No</fname>
  <lname>Name</lname>
 <address>6789 Nowhere St</address>
</customers>
Edited by kvprajapati because: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).
  • 2 Contributors
  • 1 Reply
  • 105 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by kvprajapati
Member Avatar for kvprajapati

>When I do this all the parent nodes are not being displayed properly.

To display xml content at browser embed xml markup with pre tag.

Response.Write("<pre>");
 ....
 Response.Write("</pre>");
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.