Reading XML Direct From SQL Server With An XmlReader
Reading XML Direct From SQL Server With An XmlReader
Connection string:
SELECT command:
Sub Page_Load()
'get connection string from ..\global\connect-strings.ascx user control
Dim strConnect As String
strConnect = ctlConnectStrings.SQLConnectionString
outConnect.InnerText = strConnect 'and display it
'specify the SELECT statement to extract the data as an
'XML document using SQL Server 2000's "FOR XML" option
Dim strSelect As String
strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '1861003%' FOR XML AUTO"
outSelect.InnerText = strSelect 'and display it
'create a new StringBuilder to hold the results
Dim objStrBuilder As New StringBuilder()
Try
'create a new Connection object using the connection string
Dim objConnect As New SqlConnection(strConnect)
'create a new Command using the connection object and select statement
Dim objCommand As New SqlCommand(strSelect, objConnect)
'declare a variable to hold an XmlTextReader object
Dim objXTReader As XmlTextReader
'open the connection to the database
objConnect.Open()
'execute the SQL statement against the command to fill the XmlReader
objXTReader = objCommand.ExecuteXmlReader()
'read the first result to initialize the reader
objXTReader.Read()
'and then read remainder into the StringBuilder as well
objStrBuilder.Append(objXTReader.GetRemainder().ReadToEnd())
'close the XmlReader and Connection
objXTReader.Close()
objConnect.Close()
Catch objError As Exception
'display error details
outError.InnerHtml = "* Error while accessing data." _
& objError.Message & "" & objError.Source
Exit Sub ' and stop execution
End Try
'display the results as Text to show XML elements
outError.InnerText = objStrBuilder.ToString()
End Sub
