Christian de Bellefeuille
asked on
Better way to extract data from an SQL Database in XML format?
I've been able to do what i want in ASP.NET (get records from a DB in XML format), but i would like to know if there's a better (shorter) way. I thought that "reader.toString()" would do the whole job, but i had to do it by itteration:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim dbOOO As SqlConnection
Dim sb As New StringBuilder()
Dim cmdBYSSelect As SqlCommand
Dim reader As XmlReader
dbOOO = New SqlConnection("my connection info here")
dbOOO.Open()
cmdBYSSelect = New SqlCommand("SELECT * FROM myTable FOR XML AUTO, XMLDATA", dbOOO)
reader = cmdBYSSelect.ExecuteXmlRea der()
reader.Read()
reader.ReadOuterXml()
sb.AppendLine("<?xml version=""1.0"" encoding=""utf-8"" ?>" & vbCrLf)
sb.AppendLine("<TopLevel>" & vbCrLf)
Do While reader.ReadState <> System.Xml.ReadState.EndOf File
sb.AppendLine(reader.ReadO uterXml())
Loop
sb.AppendLine("</TopLevel> ")
Page.Response.Write(sb.ToS tring())
Page.Response.End()
dbOOO.Close()
End Sub
Any idea how i could optimize that?
Thanks
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim dbOOO As SqlConnection
Dim sb As New StringBuilder()
Dim cmdBYSSelect As SqlCommand
Dim reader As XmlReader
dbOOO = New SqlConnection("my connection info here")
dbOOO.Open()
cmdBYSSelect = New SqlCommand("SELECT * FROM myTable FOR XML AUTO, XMLDATA", dbOOO)
reader = cmdBYSSelect.ExecuteXmlRea
reader.Read()
reader.ReadOuterXml()
sb.AppendLine("<?xml version=""1.0"" encoding=""utf-8"" ?>" & vbCrLf)
sb.AppendLine("<TopLevel>"
Do While reader.ReadState <> System.Xml.ReadState.EndOf
sb.AppendLine(reader.ReadO
Loop
sb.AppendLine("</TopLevel>
Page.Response.Write(sb.ToS
Page.Response.End()
dbOOO.Close()
End Sub
Any idea how i could optimize that?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
Try this
aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="GridViewList.asp x.vb" Inherits="Database_GridVie wList" %>
codebehind
Response.Write(ds.GetXml() )
Try this
aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="GridViewList.asp
codebehind
Response.Write(ds.GetXml()
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Characters such as > are replaced by >.
It also add some other stuff like <TABLE> if i look at the source.
And IE is not able to recognized it as an XML model anymore... (you know when you open an xml file with it, it show some kind of tree with colorized syntax).
Plus, using the example with WriteXml, i've noticed that i could add a parameter XmlWriteMode.IgnoreSchema to not show the schema, and it have absolutely no effect... the schema still write itself.
Any idea of what could cause all theses problems?
Thanks