 | [Imagination is more important than knowledge - Albert Einstein] |
| Using SQLXML [In SQLServer2000] To display Report [Author:Sreedhar Koganti Dt:MAR042002] |
Aim:
This Example is focused on using SQLXML functions in asp pages. We will get data from data base in xml format and Using xsl we will display data in asp page.
The purpose of this example is to show SQLServer2000 + SQLXML functions and use of those functions in web.
Note: We can also show the same example output by Using SQLXML templates. There we can directly call XML files with xsl attachment on URL.
System Requirements: IIS, ASP , SQLServer2000, SQLXML
Skills: ASP , SQLServer2000, SQLXML
ASP Code: [ We can also try this example in .Net. Since many of users requested me to show using SQL2000 functions in ASP, i choosed this. Using SqlXmlCommand we can try the same example in .Net also. Need more info on that... Contact me.
<%
Dim Stream,Connection,Command
Set Stream =Server.CreateObject("ADODB.Stream")
Set Connection =Server.CreateObject("ADODB.Connection")
Set Command = Server.CreateObject("ADODB.Command")
Connection.Open "provider=SQLXMLOLEDB.2.0;
data provider=SQLOLEDB;data source=(local);initial catalog=SampleDataBase;user id=sa;password=;"
Command.ActiveConnection = Connection
Command.Properties("ClientSideXML") = "True"
Command.CommandText = _
"<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql' >" & _
" <sql:query> " & _
" SELECT C_UN_UNIVID, C_UN_NAME,C_UN_DESCRIPTION FROM TBL_UNIV FOR XML AUTO " & _
" </sql:query> " & _
" </ROOT> "
Stream.Open
' You need the dialect if you are executing a template.
Command.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
Command.Properties("Output Stream").Value = Stream
Command.Properties("Base Path").Value = "c:\Inetpub\wwwroot\"
Command.Properties("xsl").Value = "Sample.xsl"
Command.Execute , , 1024
Stream.Position = 0
Stream.Charset = "utf-8"
Response.Write Stream.ReadText(-1)
%>
|
Discussion:
In order to show this example i took a table called tbl_univ from my data base. The code is simple.
Keynotes:
1. ClientSideXml property to True enforces the processing of the rowset on the client side.
2. Since SQL Query is specified in a template we have to set Command Dialect to 5d531cb2-e6ed-11d2-b252-00c04f681b71.
3. xsl property specifies the Sample.xsl file to use to apply the transformation.
4. One more important thing is i am using Stream in ADO, So you must have ADO2.5 or greater.
One more important file for this is Sample.xsl.
<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match = 'TBL_UNIV'>
<TR>
<TD><xsl:value-of select = '@C_UN_UNIVID' /></TD>
<TD><xsl:value-of select = '@C_UN_NAME' /></TD>
<TD><B><xsl:value-of select = '@C_UN_DESCRIPTION' /></B></TD>
</TR>
</xsl:template>
<xsl:template match = '/'>
<HTML>
<Title> Test SQL XML Example</Title>
<BODY>
<center>
<TABLE border='1' bgcolor="lightyellow">
<TR><TH colspan='3'
bgcolor="lightblue">Sample SQLXML Example for ASP
[Same Example we can try in VB6 or .Net also]</TH></TR>
<TR><TH colspan='3'<University Details</TH></TR>
<TR><TH <University ID</TH><TH >
University Name</TH><TH>University Description</TH></TR>
<xsl:apply-templates select = 'ROOT' />
</TABLE>
</center>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>
|
Enjoy the code. I would think it's simple. Have fun. I had some idea and i tried it. If you obsurve closely Stream.ReadText(-1) method is giving entire HTML file for us. So by using this logic we can dynamically generate HTML file to the users. " As a Tool ". I hope this hint is enfough to proceed to make your own components for generating HTML files's.
Download the code
Sample.xsl
Happy Programming !!!!
| <--[Author: Sreedhar Koganti] |
|
|
|