Displaying ADO Recordsets saved as XML with XSL

Printable View

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

1 Attachment(s)

Displaying ADO Recordsets saved as XML with XSL

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
May 11th, 2001, 10:17 AM
Cander

yeah. you add this to the xml doc

Code:

<?xml-stylesheet type="text/xsl" href="myxsl.xsl"?>
May 14th, 2001, 07:59 AM
mick lennon

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
May 14th, 2001, 08:39 AM
Cander

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
May 14th, 2001, 10:17 AM
mick lennon

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>
May 14th, 2001, 11:01 AM
Cander

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> <xsl:value-of select="xml/rs:data/z:row/@palletid"/> <xsl:for-each select="xml/rs:data/z:row/ChildCMD"> <xsl:value-of select="@serialno"/> </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.
May 15th, 2001, 04:01 AM
mick lennon

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
May 15th, 2001, 10:01 AM
Cander

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