XML and the SQL 2000 Server, Part 4: Introducing SqlXml 3.0 - SqlXmlCommand's RootTag Property
(Page 5 of 9 )
As discussed in Part 1, Part 2, the TSQL extensions forXML returns but a fragment. It is possible to use the SqlXmlCommand's RootTag property to add the missing root for the returned result as shown in the following code. The Length property of the stream give the number of characters returned. The result of browsing this web page is shown in the picture that follows the code. Again references to System.IO and Microsfot.Data.SqlXml should be made.
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 pub_id, price, type,ytd_sales,
titleauthor.title_id,au_ord from titles, titleauthor
where titleauthor.title_id= titles.title_id andprice>15 order by pub_id
for xml auto"
Dim strmResults As MemoryStream
'RootTag is added before calling the ExecuteStream
sxcmd.RootTag = ("PubsTitles")
strmResults = sxcmd.ExecuteStream()
Response.Write(strmResults.Length)
Dim strmReader As New StreamReader(strmResults)
TextBox1.Text = strmReader.ReadToEnd
End If
End Sub
Next: SqlXmlCommand's NameSpace Property >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
