Link to home
Start Free TrialLog in
Avatar of Christian de Bellefeuille
Christian de BellefeuilleFlag for Canada

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.ExecuteXmlReader()
        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.EndOfFile
            sb.AppendLine(reader.ReadOuterXml())
        Loop
        sb.AppendLine("</TopLevel>")
        Page.Response.Write(sb.ToString())
        Page.Response.End()
        dbOOO.Close()
End Sub

Any idea how i could optimize that?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of madhevan_pillai
madhevan_pillai
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Christian de Bellefeuille

ASKER

Both method works madhevan and azza, but i have noticed a strange effect in IE with them.

Characters such as > are replaced by &gt;.

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
Hi,

Try this

aspx

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="GridViewList.aspx.vb" Inherits="Database_GridViewList" %>

codebehind

Response.Write(ds.GetXml())

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial