0

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 __avd: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by __avd
0

>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>");
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.