?
Solved

Better way to extract data from an SQL Database in XML format?

Posted on 2007-10-18
5
Medium Priority
?
259 Views
Last Modified: 2012-06-21
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
0
Comment
  • 3
5 Comments
 
LVL 12

Accepted Solution

by:
madhevan_pillai earned 1200 total points
ID: 20106678
Hi,

You can try with a dataset

  Dim str As New System.IO.StringWriter()
        Dim ds As DataSet
        Dim sqlcon As New SqlClient.SqlConnection("your connection string")
        Dim sqladp As New SqlClient.SqlDataAdapter("your select statement", sqlcon)
        sqladp.Fill(ds)
        ds.WriteXml(str)
        str.ToString() 'will have the entire xml
0
 
LVL 1

Assisted Solution

by:azza_c
azza_c earned 800 total points
ID: 20106701
Hi cdebel,

Would it be easier to grab the data from the database into a dataset then use the dataset's GetXml() method? MSDN reference: http://msdn2.microsoft.com/en-us/library/system.data.dataset.getxml.aspx 

Something along the lines of:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim dbOOO As SqlConnection()        
        Dim cmdBYSSelect As SqlCommand()
        Dim da as SqlDataAdapter()
        Dim ds as DataSet()

        dbOOO = New SqlConnection("my connection info here")
        dbOOO.Open()
        cmdBYSSelect = New SqlCommand("SELECT * FROM myTable", dbOOO)
        da = New SqlDataAdapter(cmdBTSSelect)
        da.Fill(ds, "myDataset")
        Page.Response.Write(ds.GetXml())
        Page.Response.End()
        dbOOO.Close()
End Sub

Cheers
0
 
LVL 10

Author Comment

by:Christian de Bellefeuille
ID: 20115286
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
0
 
LVL 12

Expert Comment

by:madhevan_pillai
ID: 20121598
Hi,

Try this

aspx

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

codebehind

Response.Write(ds.GetXml())

0
 
LVL 12

Assisted Solution

by:madhevan_pillai
madhevan_pillai earned 1200 total points
ID: 20121629
sorry codebehind u have to do this

        Response.ContentType = "text/xml"
        Response.Write(ds.GetXml())
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question