XML and the SQL 2000 Server, Part 4: Introducing SqlXml 3.0 - SqlXmlCommand's ExecuteXMLReader
(Page 7 of 9 )
ExecuteXMLReader method returns the SQL 2000 Server data to the XMLTextReader, an implementation of the XMLReader class. This provides a fast forward, navigation process for the retrieved result. The code shows how the method is invoked and the result(truncated to show couple of lines) of executing this code. References to System.IO and Microsoft.Data. SqlXml are not shown here.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs)
Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then
Dim sxcmd As New SqlXmlCommand("Integrated Security=SSPI;
Packet Size=4096;Data Source=XPHTEK;
Tag with column collation when possible=False;
Initial Catalog=pubs;Use Procedure for Prepare=1;Auto Translate= True;
Persist SecurityInfo=False;Provider='SQLOLEDB.1';Workstation ID=XPHTEK;
Use Encryption for Data=False")
sxcmd.CommandText = "select au_fname, au_lname,city,
phone from authors for xml auto"
sxcmd.RootTag = "Publishers"
'declare a XMLTextReader
Dim strRes As XmlTextReader
strRes = sxcmd.ExecuteXmlReader
'do some diagnostics
Response.Write("The read state is ; " & strRes.ReadState)
Response.Write("<br>Does it have attributes? " &
strRes.HasAttributes)
Response.Write("<br>How many attribues? " & strRes.AttributeCount)
While strRes.Read
strRes.WhitespaceHandling = WhitespaceHandling.None
Response.Write(strRes.Item("au_fname") & "<br>")
End While
strRes.Close()
End If
End Sub
Using Stored Procedure & ExecuteXmlReader
Consider the following stored procedure created in the 'localhost' [SQL 2000 Server XPHTEK that is being used in all the examples] which retruns the full name of a person which has First Name and Last Name stored in one of its tables. The name of the stored procedure is fullNameXML. Notice that this would return a XML fragment
Create proc fullNameXML
as
select (au_fname+' '+au_lname) as fullName, phone
from authors
for xml auto
The CommandText property is set to execute the stored procedure and an XMLReader reads the returned results after executing the ExecuteXmlReader. A root tag is also added to the fragment. The result of browsing this page is shown after the code listing.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs)
Handles MyBase.Load 'Put user code to initialize the
page here If Not
IsPostBack Then Dim sxcmd AsNewSqlXmlCommand
("PROVIDER=SQLOLEDB.1;SERVER=XPHTEK;DATABASE=pubs;INTEGRATED SECURITY=sspi;")
sxcmd.RootTag = "FullName"
sxcmd.CommandText = "exec fullNameXML"
Dim xrdr As System.Xml.XmlReader
xrdr = sxcmd.ExecuteXmlReader
While xrdr.Read
Response.Write(xrdr.Item("fullName") & "<br/>")
End While
xrdr.Close()
End If
End Sub
Next: Using CommandType Template >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
