XML and the SQL 2000 Server, Part 4: Introducing SqlXml 3.0 - Using CommandType Template
(Page 8 of 9 )
In Part1 and Part2, templates have been discussed in detail. Please review the material on templates for a background to this topic. XML data can be retrived using templates. There are two options, in one of the options you supply the template in the form of a string to the CommandText property. In the other you give reference to a template file including its name and its path. In either case, you need to set the CommandType to the proper type as was seen in this picture. If you choose the template string then you should use the SqlXmlCommandType.template and in the other use SqlXmlCommandType. templatefile. The following code uses a string[the template] to feed the CommandText, the result follow 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
'create command object for template
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 Security Info=False;
Provider='SQLOLEDB.1';Workstation ID=XPHTEK;Use Encryption for
Data=False")
sxcmd.CommandText = "<root xmlns:
sql='urn:schemas-microsoft-com:xml-sql'><sql:query>select
(au_fname+' '+au_lname) as fullName from authors for xml
auto</sql:query></root>"
Response.Write(sxcmd.CommandText)
sxcmd.CommandType = SqlXmlCommandType.Template
'Response.Write(Server.HtmlEncode(sxcmd.CommandText))
Dim sm As MemoryStream
sm = sxcmd.ExecuteStream()
Dim strmr As New StreamReader(sm)
TextBox1.Text = (strmr.ReadToEnd)
strmr.Close()
sm.Close()
End If
End Sub
Using CommandType=TemplateFile
As mentioned previously, the CommandType is SqlXmlCommandType.TemplateFile and the path of the file needs to be indicated. In the example, a template file called TplAuthors.xml is stored in the application directory and a Server.MapPath method is used to provide its location. The code is as shown and the result follows the code
This is the TplAuthors.xml in the application folder
File Name: TplAuthors.xml
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
select (au_fname+' '+au_lname) as fullName
from authors for xml auto
</sql:query>
</root>
Now the code for data access using this template file
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.CommandType = SqlXmlCommandType.TemplateFile
sxcmd.CommandText = Server.MapPath("Tplauthors.xml")
Response.Write(sxcmd.CommandText)
Dim sm As MemoryStream
sm = sxcmd.ExecuteStream()
Dim strmr As New StreamReader(sm)
TextBox1.Text = (strmr.ReadToEnd)
strmr.Close()
sm.Close()
End If
End Sub
Next: Using the BasePath and XSLPath properties >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
