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>