XML and the SQL 2000 Server, Part 4: Introducing SqlXml 3.0 - Using the BasePath and XSLPath properties
(Page 9 of 9 )
XML data although eminently suited for transport does not have the look of HTML. This is where XSL steps in to transform XML into HTML that is universally liked as a display format. However, XSL is for transformations of not only HTML but to other formats as well. SqlXmlCommand objects properties can help transofrm the XML into presentable HTML format as shown with the following code. You may need a little backgorund on XSL and the recommended site is http://www.w3c.org/, the Mecca for everything about XML.
This code does several things. It uses a stored template file and executes the command and through its XslPath property it references a stored XSL style sheet to be included into the returned results. The resulting document is stored as an HTML file in the chosen location. The XML file follows the code in the XSL file provided to format the HTML. A truncated FullName.htm is shown in the screen shot.
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.RootTag = "author"
sxcmd.CommandText = Server.MapPath("Tplauthors.xml")
Response.Write(sxcmd.CommandText)
sxcmd.XslPath = Server.MapPath("authors.xsl")
Dim strmResults As New FileStream
("C:\FullName.htm", FileMode.Append)
sxcmd.ExecuteToStream(strmResults)
End If
End Sub
authors.xsl file
<?xml version='1.0'?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"> <xsl:template match="/"><html><body><h2>Full Name of Authors</h2><table border="1.0" bgcolor="gold"><tr><td>Full Name</td></tr><xsl:for-each select="root/authors"><tr><td><xsl:value-of select='@fullName'></xsl:value-of></td></tr></xsl:for-each></table></body></html></xsl:template></xsl:stylesheet>
Conclusions
This tutorial, 4th part in this series, is focussed on the SQLXML3.0 enhancements that make working with XML a lot easier through its support by means of several properties and methods. Only the data access part has been touched in this tutorial and of course it is also possible to make changes to the database by using some of the other items left out, notably the UpdatGram and DiffGram. These will be discussed in a forth coming tutorial.
The codes shown in this tutorial were tested on Visual Studio .NET 2003 on XP Professional platform with the backend server running in the same box as the IIS 5.0. If the server references are made correctly in the connection string, cutting and pasting the code should work. The imports should include the System.IO and Microsoft.Data.SqlXml. However, when files are written to hard disk, permissions should be present and this is also true for database access and while executing stored procedures.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
