XML and the SQL 2000 Server, Part 4: Introducing SqlXml 3.0 - SqlXmlCommand's NameSpace Property

(Page 6 of 9 )

Just like adding the RootTag, it is also possible to add a Namespace using the NameSpaces property of the SqlXmlCommand object. Adding a NameSpace would help in referencing a schema for the XML results. The code for adding a namespace is shown together with the result following the code

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
'adds a root tag
sxcmd.RootTag = ("PubsTitles")
'add a namespace
sxcmd.Namespaces = "xmlns=""www.mysorian.com"""
strmResults = sxcmd.ExecuteStream()
Response.Write(strmResults.Length)
Dim strmReader As New StreamReader(strmResults)
TextBox1.Text = strmReader.ReadToEnd
End If
End Sub

SqlXmlCommand's ExecuteToStream Method

The returned XML data can be added to an existing stream which would help in persisting the data. Assuming an existing FileStream, it is possible to use the ExecuteToStream method of the SqlXmlCommand to add the data to the file stream. Herein, a new FileStream is created defined by the path and filename, filemode and the ExecuteToStream method is called. The code is shown here, but remember to add the imports mentioned earlier. The result of this code is persisted to a file, shown by the link after the code.

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"
sxcmd.RootTag = "Publishers"
Dim strmResults As New FileStream
("C:\streamwritten.xml", FileMode.Append)
sxcmd.ExecuteToStream(strmResults)
strmResults.Close()
Response.Write("Retrieved")
End If
End Sub

View the result[streamwritten.xml] of executing the above code

Next: SqlXmlCommand's ExecuteXMLReader >>
 

More MS SQL Server Articles
More By Jayaram Krishnaswamy

Comments On: XML and the SQL 2000 Server, Part 4: Introducing SqlXml 3.0

>>> Be the FIRST to comment on this article!