I previously wrote about exporting XML from Microsoft SQL Server and using it when generating reports from 24SevenOffice.com. Now i will show you how this is done.
First we open a ADO connection and recordset just like we normally do.
' OPEN REPORT IN RECORDSET
set c_db = server.CreateObject("ADODB.Connection")
c_db.ConnectionString = Application.Contents.Item("ConnString")
c_db.Open()
Set ObjADORS = Server.CreateObject("ADODB.Recordset")
ObjADORS.Open sql, c_db,adOpenStatic,adLockReadOnly
set objStream = server.CreateObject("ADODB.Stream")
objStream.Charset = "ISO-8859-1"
' SAVE RECORDSET TO ADO STREAM AS XML
ObjADORS.Save objStream, adPersistXML
ObjADORS.Close
Set ObjADORS = Nothing
c_db.Close
set c_db = nothing
Look at the code above and youl will recognise all lines except for one - objADORS.Save objStream.adPersistXML. What i am doing is saving the ADO Recordset to ADO Stream, but not as ADO Recordset but in XML format (adPersistXML).
The next thing we need to do is get the XSLT and transform the XML using the XSLT:
' LOAD XSLT
set objXSLT = server.CreateObject("MSXML2.FreeThreadedDOMDocument.4.0")
objXSLT.createProcessingInstruction "xml", "version=""1.0"" encoding=""ISO-8859-1"""
objXSLT.async = false
objXSLT.load("file.xslt")
' LOAD THE XSLT TEMPLATE TRANSFORMER
set objXSLTTemplate = server.CreateObject("MSXML2.XSLTemplate.4.0")
objXSLTTemplate.stylesheet = objXSLT
set objXSLT = Nothing
' Load the XSLT processor
set xmlPro = objXSLTTemplate.createProcessor()
set objXSLTTemplate = Nothing
' USE THE ADO STREAM
xmlPro.input = objStream
objStream.Close
set objStream = Nothing
set objXMLDOM = Nothing
' Output to response
xmlPro.output = Response
' Transform XML using XSLT
xmlPro.transform
set xmlPRo = Nothing
The XSLT file looks like this:
<?xml version="1.0" encoding="ISO-8859-1"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"> <xsl:output method="xml" media-type="text/xml" encoding="ISO-8859-1" /> <xsl:template match="/"> <xsl:element name="Entries"> <xsl:for-each select="//rs:data/z:row"> <xsl:element name="Entry"> <xsl:attribute> <xsl:value-of select="@DatabaseFieldName"/> </xsl:attribute> </xsl:element> </xsl:for-each> </xsl:element> </xsl:template> </xsl:stylesheet>
What you need is the //rs:data/z:row - this will list all the fields in the recordset. The XSLT transformer will then populate the final XSLT document with values from the XML.