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()
pobjSQLCommand = pobjSQLConnection.CreateCommand
pstrSQL = "sp_GETCustomersNamesAsXML"
pobjSQLCommand.CommandText = pstrSQL.ToString
pobjXMLReader = pobjSQLCommand.ExecuteXmlReader()
'Blow by errors if you want
Tip Submitted By: David McCarter