This is the mail archive of the
xsl-list@mulberrytech.com
mailing list .
SQL 2000 query and doing an XML + XSL Transform on the server
- To: xsl-list at lists dot mulberrytech dot com
- Subject: [xsl] SQL 2000 query and doing an XML + XSL Transform on the server
- From: "David Marsh" <DMarsh at fscnet dot com>
- Date: Thu, 22 Feb 2001 14:55:57 -0700
- Reply-To: xsl-list at lists dot mulberrytech dot com
I have a problem here.
I am using a form on a web page to get two values: GroupID and UserID.
I'm passing these two values to SQL Server 2000 to an XML template file to run a query
passing these two parameters and returing XML. I then wanted to format the XML with XSL on
the server, returning HTML to the client. I was planing on using Microsoft's XSLISAPI 2.1
dll to do the server-side transformations. However, I found out recently by reading the
known issues for the XSLISAPI filter that it conflicts with the SQL Server 2000 ISAPI
filter.
So, how to run a query and get back XML and format it on the server? I thought ASP, but I
haven't done any ASP at all so I am kinda scratching my head.
Here is a really simple example:
I am using an HTML page to pass two parameters to the XML template file:
<HTML>
<HEAD>
<TITLE>Sample For submit for Template Query</TITLE>
</HEAD>
<BODY>
Enter the GroupID and UserID for the Query.
<FORM action="IISDIR/template/xmlFile.xml" method="POST">
<B>GroupID</B>
<INPUT type="text" name="GroupID">
<BR>
<B>UserID</B>
<INPUT type="text" name="UserID">
<INPUT type="submit">
</FORM>
</BODY>
</HTML>
Then My XML Template looks like this:
<?xml version='1.0'?>
<?xml-stylesheet type="text/xsl" href="xslFile.xsl"?>
<Root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
<sql:param name="GroupID"></sql:param>
<sql:param name="UserID"></sql:param>
</sql:header>
<sql:query>
EXEC StoredProcedure @GroupID, @UserID
</sql:query>
</Root>
The XSL file formats it it sends it to the client. If the client uses IE5+ with the MSXML3
parser then everything would be fine, but that's not reality. Normally the XSLISAPI2.1
filter would catch the file with the XML extension and do server-side rendering, but SQL
2000 catches it first.
I then thought that I could try ASP (which I know nothing about). so I came up with this in
ASP:
<%
Dim xmlDocument, xml, xslDocument, xsl
' Set the source and style sheet locations here
xmlDocument = Request.ServerVariables("APPL_PHYSICAL_PATH") +
"\IISDIR\Template\xmlFile.xml"
xslDocument = Request.ServerVariables("APPL_PHYSICAL_PATH") +
"\IISDIR\Template\xslFiley.xsl"
' Load the XML
Set xml = Server.CreateObject("Microsoft.XMLDOM")
xml.async = false
xml.load(xmlDocument)
' Load the XSL
Set xsl = Server.CreateObject("Microsoft.XMLDOM")
xsl.async = false
xsl.load(xslDocument)
Response.Write(xml.transformNode(xsl))
%>
However I am running into two problems:
1) how to execute the stored procedure in the template file (and either keep it in memory or
write it to a physical file) so that I can do the transformation with XSL. Right now it
just tries to format the two files, and the stored procedure isn't run.
2) how to pass the parameters to the XML template file using ASP so that the stored
procedure can run with the two parameters.
Has anyone been oin this similar situation?
If I knew ASP then I might know how to do it, but instead I'm on the fast-track, figure how
to do in in ASP in a day, schedule.
If anyone knows any other work-around then it would be appreciated. I am a newbie.
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list