TopXML SQL XML library : How to call out to a COM component's method through XSLT

Home Page

Add/Edit your code items

Search the code library

Browse for the code library

SQL XML
SQL XML
SQL XML Contents
How to select DISTINCT items from XML via XSLT
How to convert Word (RTF) documents to XML for auto publication
Creating SQL Statements with XSLT
SQL straight to XML w/ transform
History Of XML
History Of XML And What Is XML.
Order Automation
You enjoy while you are away and let your machine do the job for you.
SQL Server 2000 User Defined Functions - A Powerful concept
XML/XSLT Maker
Generic ADO recordset to HTML table using XML / XSL
How to display the first three nodes of a XML file with XmlDocument?
What is the usage of the XmlDataDocument?

<< SEO System.XML >>

By	Mark Wilson I am the creator of TopXML. I am available for international and local (Australia) contracts. I am a Solution Architect/Business Analyst. I have worked in IT in several countries (NZ, Australia, South Africa, UK) building and training teams for government and very large non-governmental organizations. I am ex-Microsoft Consulting Services. I wrote the first book on Microsoft XML published in 2000 called XML Programming with VB and ASP. Most recently I have been building tools for the SEO industry. Ask me for a 37 point SEO health-checkup for your website.
First Posted	05/13/2002
Times viewed	205

How to call out to a COM component's method through XSLT

Summary This snippet shows you how to call into a COM component's method from within an XSL Stylesheet. Note that the creation of the component is not within the scope of this small article, and is assumed to be part of your basic skills.

You can call out into a COM component's method from within your XSL Stylesheet as shown in the following example (using JavaScript and ASP 3.0 on IIS 5.0/W2K): 1. in your ASP page, create the COM object (note that "SQL2XML.Orders" is just an example of a dll I created in VB6) var myObj = Server.createObject("SQL2XML.Orders"); This COM component accesses the SQL Pubs database and executes a Stored Procedure against it to get some specific Order Details. 2. then, load the XSL Stylesheet and get it compiled var xslProc = getProcessor("myCust.xsl"); function getProcessor(transformName) { var xslDoc = new ActiveXObject("MSXML2.FreeThreadedDOMDocument"); var xslTemplate = new ActiveXObject("MSXML2.XSLTemplate"); xslDoc.async = false; xslDoc.load(Server.mapPath(transformName)); xslTemplate.stylesheet = xslDoc; xslProcessor = xslTemplate.createProcessor(); return xslProcessor; } 3. now, add your COM object to the XSL processor xslProc.addObject(myObj,"urn:schemas-microsoft-com:xml-sql"); 4. then, add parameters when you are ready to perform the XSLT // transform the XML source and display it transformData(srcDoc,xslProc,strConn,strQuery); function transformData(srcDoc,xslProc,strConn,strQuery) { xslProc.input = srcDoc; // here you can add any parameter you like xslProc.addParameter("strConn", strConn, ""); xslProc.addParameter("strQuery", strQuery, ""); xslProc.transform(); Response.Write(xslProc.output); return true; } Note that srcDoc is declared like this and gets loaded the XML source data: var srcDoc = Server.CreateObject("MSXML2.ServerXMLHTTP"); and that strConn and strQuery are strings and define the connection details to the SQL database when the XSLT is performed. See the XSL Stylesheet below. Finally, I must mention also that this example uses version-independent ProgIDs, which you may wish to change to version-dependent ones for version 3.0 or 4.0 of MSXML. If you do so, take care that you do this for ALL instantiated dlls, and in a consistent way, i.e. do not use different versions as this will generate problems.

Additional information
The XSL Stylesheet is as follows: ************************ <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:gr="urn:schemas-microsoft-com:xml-sql" extension-element-prefixes="gr msxsl" version="1.0"> <xsl:output method="html" media-type="text/html"/> <xsl:param name="strConn" select="'driver={SQL Server}; server=localhost;uid=sa;pwd=password;database=Northwind'"/> <xsl:param name="strQuery" select="'ReturnOrderCount'"/> <xsl:param name="CustId" select="''"/> <xsl:template match="/"> <xsl:apply-templates/> </xsl:template> <xsl:template match="root"> <table border="1" width="900" cellpadding="2"> <tr bgcolor="#FF00AA"> <xsl:for-each select="customer[1~/@*"> <td valign="top"> <font color="#ffffff"> <b><xsl:value-of select="local-name()"/></b> </font> </td> </xsl:for-each> </tr> <xsl:apply-templates/> </table><br/><hr/><br/> </xsl:template> <xsl:template match="customer"> <xsl:variable name="bg"> <xsl:choose> <xsl:when test="(position() mod 2) = 0"> <xsl:text>#efefef</xsl:text> </xsl:when> <xsl:otherwise> <xsl:text>#ffffff</xsl:text> </xsl:otherwise> </xsl:choose> </xsl:variable> <xsl:variable name="strCustId"> <xsl:value-of select="./@CustomerID"/> </xsl:variable> <tr bgcolor="{$bg}"> <xsl:for-each select="./@*"> <td valign="top"> <xsl:value-of select="."/> <xsl:if test="position()=2"> <br/> <xsl:text>OrderCount : </xsl:text> <xsl:variable name="custcount" select="gr:ReturnOrderCount(string($strConn), string($strQuery),string($strCustId))"> </xsl:variable> <xsl:value-of select="$custcount"/> </xsl:if> </td> </xsl:for-each> </tr> </xsl:template> </xsl:stylesheet> ************************ The xmlns:gr namespace is used here to define a namespace for the dll: <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:gr="urn:schemas-microsoft-com:xml-sql" extension-element-prefixes="gr msxsl" version="1.0"> The Customer ID is formed in the part <xsl:variable name="strCustId"> <xsl:value-of select="./@CustomerID"/> </xsl:variable> Then, the ID is used to perform a call to the COM object: <xsl:variable name="custcount" select="gr:ReturnOrderCount( string($strConn),string($strQuery),string($strCustId))"> </xsl:variable> and is displayed like this: <xsl:value-of select="$custcount"/> Note that the parameters have default values and that the variable CustId is given a value inside the XSL Stylesheet at run-time.

Further additional information
An example of an XML Result Fragment is like the following: <root xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" City="Berlin" Country="Germany"/> </root> This XML is created using SQLXML 3.0 on SQL Server 2000, by executing an SQL Template Query, from which an example is like this: <root xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:header> <sql:param name="CustId"/> </sql:header> <sql:query> IF @CustId IS NULL BEGIN SELECT CustomerID,CompanyName,ContactName, ContactTitle, City, Country FROM Customers as customer FOR XML AUTO END ELSE BEGIN SELECT CustomerID,CompanyName,ContactName, ContactTitle, City, Country FROM Customers as customer WHERE CustomerID LIKE @CustId + '%' FOR XML AUTO END </sql:query> </root> As you can see, the parameter CustId is passed from the URL QueryString into the template query, and then SQL uses this parameter to get specific Customer information from the database, and returns it like XML. If you do not like the format SQL returns, and want for example the attributes as elements, change the line FOR XML AUTO into FOR XML AUTO, ELEMENTS This way you can turn your SQL relational database into an XML database, and access it over the Internet using HTTP, and passing parameters in the URL's QueryString.

Rate this article on a scale of 1 to 10 (0 votes, average 0)

Your vote :

<< SEO System.XML >>


Leave a comment for this article
Your name
Your email (optional)
Your comment
Optional: Upload an attachment
Enter the code shown:



	Recent Jobs Software Developers Needed in Charlotte, Sr. Software Engineer - Analytics Immediate Mainframe openings for Chennai Immediate TANDEM-TAL openings for Chenna Immediate ASP.NET/C# Openings for Chandi View all Jobs (Add yours) View all CV (Add yours)

	conference call services swimming pool builder affordable conference calls water softener Teleconference Host Department NOLIMIT Web Hosting MSN sunglasses

	Partners Dream.In.Code dotNet Slackers dotNet Spider Your HTML Source VisualBuilder.com DevGuru Planet Source Code ZVON.ORG Web Design ASPAlliance XML Pitstop Scripts The Spot 4 SAP Bitshop Web Hosting

Email TopXML