How do I generate an XML file from stored procedure?

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



LVL 1
gianitooAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Luis PérezSoftware Architect in .NetCommented:
All that you want to do is to save the file from a string that you have obtained from your stored procedure?
0
gianitooAuthor Commented:
yes...using the idea below

my connection--------
   Dim strConn2 As String = ConfigurationManager.ConnectionStrings("test").ConnectionString
        Dim conn2 As New SqlConnection(strConn2)
 
        conn2.Open()
     
        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
     
       
        conn2.Close()
        conn2.Dispose()
---------------------------

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

 Dim writer As New XmlTextWriter(Response.OutputStream, Encoding.UTF8)
        writer.WriteStartDocument()
     
      Response.Write(Session("xml"))

        writer.WriteEndDocument()
        writer.Close()
0
MitzsCommented:
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????
0
MitzsCommented:
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
      Return
   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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.