[xsl] SQL 2000 query and doing an XML + XSL Transform on the server

Subject: [xsl] SQL 2000 query and doing an XML + XSL Transform on the server
From: "David Marsh" <DMarsh@xxxxxxxxxx>
Date: Thu, 22 Feb 2001 14:55:57 -0700
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


Current Thread