Displaying ADO Recordsets saved as XML with XSL

**mick lennon** · May 11th, 2001, 03:06 AM

Hi Folks
I use the following to save my heirarchical recordset as XML.

Dim db2 As New adodb.Connection
Dim palletconnstring As String
Set adoRSForXML = New Recordset
palletconnstring = "PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;dsn=mltest;uid=;pwd=;"
db2.Open palletconnstring

adoRSForXML.Open "SHAPE {select palletid,tla,rev,datecreated,MovedToFG,MovedToHUB,HubNAME,Shipped from PalletDetails2 where palletid = '" & Trim(adoPrimaryRS!palletid) & "' Order by palletid} AS ParentCMD APPEND ({select serialno,palletid from Serialno2 Order by serialno } AS ChildCMD RELATE palletid TO palletid) AS ChildCMD", db2, adOpenStatic, adLockOptimistic

adoRSForXML.Save "pallet_serialno.sav", adPersistXML
adoRSForXML.Save s, adPersistXML

I get the attached XML file. I want to be able to create an HTML report at run time using an XSL script.
How do I associate and XSL file with it at run time?
Do I have to add any reference to the XSL file inside the XML file?

Cheers folks

**Cander** · May 11th, 2001, 10:17 AM

yeah. you add this to the xml doc

Code:

<?xml-stylesheet type="text/xsl" href="myxsl.xsl"?>

**mick lennon** · May 14th, 2001, 07:59 AM

Cheers Cander
Any thoughts on how I can extract the <childcmd> part of the heirarchical recordset with XSL. What code do I need?

Thanks again

**Cander** · May 14th, 2001, 08:39 AM

kind of depends what you want to do, but here is what you would use to display the serialno attribute of each ChildCMD node..

Code:

<xsl:for-each select="data/row/ChildCMD">
<xsl:value-of select="@serialno"/>
</xsl:for-each>

you can also do an if check on a value and show the palletid for selected item or whatever ..like this

Code:

 <xsl:if match=".[@serialno='PKB15S0747 ']">
<xsl:value-of select="@palletid"/>
</xsl:if>

Does this help? et me know if you need anything else

**mick lennon** · May 14th, 2001, 10:17 AM

Cander your help is really appreciated.

I created an application which among other things created a data report based on the data environment. Now Im told the application will be used elsewhere. Using the Data Environment means the connection string is built into the App. No Good!. So all I want is to print a simple HTML report on one page showing the pallet information and then the serial numbers that are on it.

(1) I convert the SHAPE recordset into XML.
(2) I include the xsl file reference in the XML file.
(3) Oh **** how do I create a XSL file for a heirarchical recordset.

I know you showed me how to extract the childcmd but I couldnt get it to work. Could you show me the XSL code to write out the parent pallet info then a list of the serialno children.

<rs:data>
- <z:row palletid="DUN30" tla="TH6AE-AZ" rev="A12" datecreated="2001-04-24T10:26:35.920000000">
<ChildCMD serialno="PKB15S0747" palletid="DUN30" />
<ChildCMD serialno="PKB15S0798" palletid="DUN30" />
<ChildCMD serialno="PKB15S0885" palletid="DUN30" />
<ChildCMD serialno="PKB15S0902" palletid="DUN30" />
<ChildCMD serialno="PKB15S0907" palletid="DUN30" />
<ChildCMD serialno="PKB15S0931" palletid="DUN30" />
</z:row>
</rs:data>

**Cander** · May 14th, 2001, 11:01 AM

Here you go man

just make a blank xsl file and copy and paste this into it

Code:

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
	<xsl:template match="/">	
	<html>
		<body>
			<font color="blue"><xsl:value-of select="xml/rs:data/z:row/@palletid"/></font><br/>
			<xsl:for-each select="xml/rs:data/z:row/ChildCMD">
				<font color="red"><xsl:value-of select="@serialno"/></font><br/>
			</xsl:for-each>
		</body>
	</html>
	</xsl:template>
</xsl:stylesheet>

also make sure this

Code:

<?xml-stylesheet type="text/xsl" href="myxsl.xsl"?>

is at the very top of your XML document.

**mick lennon** · May 15th, 2001, 04:01 AM

Hi Cander
One more thing. What syntax should I use to output the other fields in the pallet(parent) i.e. tla, datecreated, rev in the form

palletid tla datecreated rev
serialno
serialno
serialno
serialno
etc.

Cheers Cander

**Cander** · May 15th, 2001, 10:01 AM

simply change the attribute name which is the word after the @

Code:

<xsl:value-of select="xml/rs:data/z:row/@palletid"/>
<xsl:value-of select="xml/rs:data/z:row/@tla"/>
<xsl:value-of select="xml/rs:data/z:row/@datecreated"/>

and so on

Thread: Displaying ADO Recordsets saved as XML with XSL

Thread Tools

Display

Displaying ADO Recordsets saved as XML with XSL

Posting Permissions