How do I output stored procedure to XML?

[Mark B]

What amendments to the ASP.NET 3.5. VB code below output the MSSQL stored
procedure dataset result to XML (as a response stream)?

(I also guessing to use Response.Conten tType = "text/xml"):
Imports System.Data.Sql Client
Imports System.Data

Partial Class pages_default
Inherits System.Web.UI.P age

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load

'Output stored procedure result as XML
'--------------------------------------------

Dim sqlConnection1 As New
SqlConnection(s fGeneral.fGetCo nnectionString( ))
Dim cmd As New SqlCommand

Dim dr As SqlDataReader = Nothing
cmd.Connection = sqlConnection1
cmd.CommandText = "uspXMLSour ce"

cmd.CommandType = CommandType.Sto redProcedure
cmd.Parameters. AddWithValue("@ EnterLanguageCo de",
Session("strLan guageSetting"))

Try
sqlConnection1. Open()
dr = cmd.ExecuteRead er
If dr.HasRows Then
dr.Read()
End If
Catch ex As SqlException
Throw ex
Finally
sqlConnection1. Close()
sqlConnection1. Dispose()
End Try
End Sub
End Class

[Alexey Smirnov]

On Oct 8, 2:27*am, "Mark B" <none...@none.c omwrote:

What amendments to the ASP.NET 3.5. VB code below output the MSSQL stored
procedure dataset result to XML (as a response stream)?

(I also guessing to use Response.Conten tType = "text/xml"):

Imports System.Data.Sql Client
Imports System.Data

Partial Class pages_default
* * Inherits System.Web.UI.P age

*Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load

* * * * 'Output stored procedure result as XML
* * * * '--------------------------------------------

* * * * Dim sqlConnection1 As New
SqlConnection(s fGeneral.fGetCo nnectionString( ))
* * * * Dim cmd As New SqlCommand

* * * * Dim dr As SqlDataReader = Nothing
* * * * cmd.Connection = sqlConnection1
* * * * cmd.CommandText = "uspXMLSour ce"

* * * * cmd.CommandType = CommandType.Sto redProcedure
* * * * cmd.Parameters. AddWithValue("@ EnterLanguageCo de",
Session("strLan guageSetting"))

* * * * Try
* * * * * * sqlConnection1. Open()
* * * * * * dr = cmd.ExecuteRead er
* * * * * * If dr.HasRows Then
* * * * * * * *dr.Read()
* * * * * * End If
* * * * Catch ex As SqlException
* * * * * * Throw ex
* * * * Finally
* * * * * * sqlConnection1. Close()
* * * * * * sqlConnection1. Dispose()
* * * * End Try
* * End Sub
End Class

Use DataSet, it can retrieve XML from relational data.

Example:

Dim ds As New dataset
Dim connStr As String = "database=North Wind;Data
Source=localhos t;User id=sa;password= sa"
Using conn As New SqlConnection(c onnStr)
Dim command As New SqlCommand("sel ect * from customers",
conn)
conn.Open()
ds.DataSetName = "Customers"
ds.Load(command .ExecuteReader( ),
LoadOption.Over writeChanges, "customers" )
Response.Conten tType = "text/xml"
ds.WriteXml(Res ponse.OutputStr eam)
End Using

[Mark B]

Thanks very much Alexey.