How do I generate an XML file from stored procedure?

Posted on 2008-11-11
Last Modified: 2012-05-05
This Stored Procedure "wmBranchListXMLSp" generates my xml data on the fly.  (sample below)
Well most of the hard work was created from business logic and would  like help to create out of this an xml file called   XMLFile.xml
Is this possible fromthis scenario?

<marker lat="61.618207" lng="-149.119613" label="AK" exist="1"/><marker lat="39.011900" lng="-121.356900" label="CA" exist="1"/><marker lat="33.392880" lng="-84.822947" label="GA" exist="1"/><marker lat="39.807710" lng="-86.337590" label="JH" exist="1"/><m

Question by:gianitoo
    LVL 25

    Expert Comment

    by:Luis Pérez
    All that you want to do is to save the file from a string that you have obtained from your stored procedure?
    LVL 1

    Author Comment

    yes...using the idea below

    my connection--------
       Dim strConn2 As String = ConfigurationManager.ConnectionStrings("test").ConnectionString
            Dim conn2 As New SqlConnection(strConn2)
            Dim cmd2 As New Data.SqlClient.SqlCommand("wmBranchListNoXMLSp", conn2)
            cmd2.CommandType = CommandType.StoredProcedure

            'cmd2.Parameters.AddWithValue("@item", Request.QueryString("option"))
            Dim dr As Data.SqlClient.SqlDataReader = Nothing
            dr = cmd2.ExecuteReader()
            While dr.Read()
                Session("xml") = dr("lat")
            End While

    -----on page load-----------------

     Dim writer As New XmlTextWriter(Response.OutputStream, Encoding.UTF8)

    LVL 4

    Expert Comment

    Title: How do I generate an XML file from stored procedure?  ---> This Stored Procedure "wmBranchListXMLSp" generates my xml data on the fly.  
    What exactly do you need  help for????
    LVL 4

    Accepted Solution

    ignore my previous post.
    Another way is to get your data in dataset and use the following function to create a xml file.
     Private Sub WriteXmlToFile ( thisDataSet As DataSet )
       If thisDataSet Is Nothing Then
       End If
       ' Create a file name to write to.
       Dim filename As String = "myXmlDoc.xml"
       ' Create the FileStream to write with.
       Dim myFileStream As New System.IO.FileStream _
          ( filename, System.IO.FileMode.Create )
       ' Create an XmlTextWriter with the fileStream.
       Dim myXmlWriter As New System.Xml.XmlTextWriter _
          ( myFileStream, System.Text.Encoding.Unicode )
       ' write to the file with the WriteXml method.
       thisDataSet.WriteXml ( myXmlWriter )
       myXmlWriter.Close ( )
    End Sub

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now