SQL 2000 query and doing an XML + XSL Transform on the
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
PURCHASE STYLUS STUDIO ONLINE TODAY!
Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!
Download The World's Best XML IDE!
Accelerate XML development with our award-winning XML IDE - Download a free trial today!
Subscribe in XML format