One of the great new features with SQL Server 2000 is that it can directly output XML from a stored procedure. Then as a developer, you just need to deal with an xml document and all if the built-in features. The code below makes it easy to get xml from a stored procedure or an SQL statement using “FORM XML AUTO” at the end.
Dim pobjSQLConnection As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("dbconnect"))
Dim pobjSQLCommand As SqlClient.SqlCommand
Dim pobjXMLReader As System.Xml.XmlReader
Dim pobjXML As New System.Xml.XmlDataDocument()
Try
pobjSQLConnection.Open()
pobjSQLCommand = pobjSQLConnection.CreateCommand
pstrSQL = "sp_GETCustomersNamesAsXML"
pobjSQLCommand.CommandText = pstrSQL.ToString
pobjXMLReader = pobjSQLCommand.ExecuteXmlReader()
pobjXMLReader.Read()
pobjXML.LoadXml(pobjXMLReader.ReadOuterXml)
pobjSQLConnection.Close()
Catch
'Blow by errors if you want
Finally
pobjSQLCommand.Dispose()
pobjSQLConnection.Dispose()
End Try
Tip Submitted By: David McCarter