ADO xml transforming using XSL

[chigley]

I am new to XML. I have created an XML file from an ADO recordset, and an XSL file to transform it, and am using vbscript to use the MSXMLDOM object to do the transforming. I have two templates in the XSL file match="/" and match="z:row". The root template produces the header output fine, but hte z:row match does nothing.

I have been reading solidly for 3 days now, and cannot see what I am doing wrong. I have also found example code on the web, that strongly resembles what I am trying to do. Can someone give me an example of an XSL file that transforms all z:rows of an ADO generated XML file to HTML text fields, setting the value of the text field to the value of the z:row first field?

Thanks in advance

Charlie

 

[JontyMC]

Post your XML and XSL. Most likely this is a namespace issue.

Jon

"I don't regret this, but I both rue and lament it.

 

[chigley]

Thanks Jon,

The XML excerpt >>>

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'>
<s:AttributeType name='PurchaseID' rs:number='1' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rsrecision='10' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='OrgID' rs:number='2' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rsrecision='10' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='CustID' rs:number='3' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rsrecision='10' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='Scope' rs:number='4' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='8'/>
</s:AttributeType>
<s:AttributeType name='ScopeDefinition' rs:number='5' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='5000'/>
</s:AttributeType>
<s:AttributeType name='CustomerName' rs:number='6' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='PurchaseRef' rs:number='7' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='Address1' rs:number='8' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='60'/>
</s:AttributeType>
<s:AttributeType name='Address2' rs:number='9' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='60'/>
</s:AttributeType>
<s:AttributeType name='Address3' rs:number='10' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='60'/>
</s:AttributeType>
<s:AttributeType name='PostTown' rs:number='11' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='60'/>
</s:AttributeType>
<s:AttributeType name='Region' rs:number='12' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='60'/>
</s:AttributeType>
<s:AttributeType name='ZipCode' rs:number='13' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='20'/>
</s:AttributeType>
<s:AttributeType name='Phone1' rs:number='14' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='40'/>
</s:AttributeType>
<s:AttributeType name='Phone2' rs:number='15' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='40'/>
</s:AttributeType>
<s:AttributeType name='Website' rs:number='16' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='Email' rs:number='17' rs:nullable='true' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row PurchaseID='7342' OrgID='22423' CustID='132505' Scope='Full' CustomerName='GB products' PurchaseRef='D103409 '
Address1='Hazelgrove Cottage' Address2='Haslemere Road' Address3='Brook' PostTown='Godalming' Region='Surrey'
ZipCode='GU8 5LB' Phone1='01428 687720'/>
<z:row PurchaseID='303' OrgID='6813' CustID='38216' Scope='Full' CustomerName='Russel Underwood Fitted Kitchens'
PurchaseRef='D106481 ' Address1='12 Easedale Drive' Address2='' Address3='' PostTown='Southport' Region='Merseyside'
ZipCode='PR8 3TL' Phone1='07970613205'/>
<z:row PurchaseID='9714' OrgID='31411' CustID='158678' Scope='Full' CustomerName='Calelec' PurchaseRef='D106957 '
Address1='7 Buxton Road' Address2='' Address3='' PostTown='Ramsgate' Region='Kent' ZipCode='CT12 6QF' Phone1='01843 590 136'/>

*************************************

and the XSL in its current form (using a for each at the mo but you get the idea)

<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl='

http://www.w3.org/1999/XSL/Transform'

xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema' version='1.0'
>
<xslutput method="xml" indent="no" omit-xml-declaration="yes"/>
<xsl:template match='/'>
<TransferSet schemename="NICEIC"

email="not_yet_known@niceic.com"
password="kapwkp1"
website="

http://www.niceic.com">

<xsl:apply-templates select="xml"/>
</TransferSet>

<SchemeScope scope="FULL"
scopedefinition=""
processinginstruction="deleteall">

</SchemeScope>

<xsl:for-each select="rs:data/z:row">

<Company name="Some company name">

</xsl:for-each>


</xsl:template>

</xsl:stylesheet>

 

[JontyMC]

The context is the root, so you'll need to select:

<xsl:for-each select="xml/rs:data/z:row">
  .....
</xsl:for-each>

Jon

"I don't regret this, but I both rue and lament it.

 

[chigley]

Output is still the same I'm afraid >>>

<TransferSet schemename="NICEIC" email="not_yet_known@niceic.com" password="kapwkp1" website="

http://www.niceic.com"

xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"></TransferSet><SchemeScope scope="FULL" scopedefinition="" processinginstruction="deleteall" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"></SchemeScope>

 

[JontyMC]

It works for me when you use select="xml/rs:data/z:row". Your posting of XML and XSL must be missing something.

Jon

"I don't regret this, but I both rue and lament it.

 

[chigley]

I am beginning to wonder what I am doing wrong. The XML is posted as it is here, and I have tried your XSL and various generic ones, to no avail. It just seems to not match any z:rows. I wondered if my machine is playing up. Running WIN2K sp4, with IE6. Will try another machine in the mean time.

 

[chigley]

Tried it on another pc and it worked fine. Always the way isn't it? Spent over a week on this :-(. Thanks for your help, your code works perfectly. Now I've got to try and figure out what is wrong with my PC!?!?!?

 

[JontyMC]

How are you doing the transform?

Jon

"I don't regret this, but I both rue and lament it.

 

[chigley]

In vbscript thusly >>>>>

FUNCTION doTransform(filename, styleSheet, output)

'Load the XML
set xml = CreateObject("Microsoft.XMLDOM")
xml.async = false
xml.load(filename)

'Load the XSL
set xsl = CreateObject("Microsoft.XMLDOM")
xsl.async = false
xsl.load(styleSheet)


Set FSO = CreateObject("Scripting.FileSystemObject")
Set FO = FSpentextfile(output, 2, TRUE, 0)

FO.write(xml.transformNode(xsl))

msgbox err.description

FO.Close

Set FO = nothing
SET FSO = nothing
Set xml = nothing
Set xsl = nothing

END FUNCTION

 

[tsuji]

[1]
[tt] <xsl:for-each select="[red]xml/[/red]rs:data/z:row">
<Company name="Some company name" [highlight]/[/highlight]>
</xsl:for-each>
[/tt]
[2] But naming the root as xml is not an excellent idea. It may run into trouble some time in other kind of operations. You should change that name.

[3] You do not need to call upon scripting.filesystemobject if you get a well-formed xml string. But apparently, it is not for the moment. Once it is, you can use an instance of DOMDocument to parse the string with loadxml method and then use save method. But, for the moment, it lacks top level root element and you do need something like FSO to do the job.

 

[chigley]

OK this is really weird, and work are getting stroppy about it :-(

I have run this code on 3 machines. The results are as follows :

My PC - It no worka
My Bosses PC - It worka
Designated Server - It no worka.

This is a 33% success rate, which is not very good. I have downloaded all the service packs and versions of msxml and have tried invoking all the different versions, 3,4,5,6 etc.

I have noticed that the output line states UTF-16 yet the XSL specifies UTF-8. Could this be the problem possibly.

Growing ever desperate for a solution ....

 

[tsuji]

If you have not close the company tag, how can it work? It is the parseerror you have not bothered to capture! (Or you have taken into account of my [a] for your statement of 33%? you don't say so explicitly, who know... It risks to turn round and round, nobody knows any other is talking about.)

[tt]'etc etc
xml.load(filename)
if xml.parseError.errorCode <>0 then
set myErr = xml.parseError
WScript.Echo "You have error " & myErr.reason
end if
'etc etc
xsl.load(styleSheet)
if xsl.parseError.errorCode <>0 then
set myErr = xsl.parseError
WScript.Echo "You have error " & myErr.reason
else
'Echo back XSLT output to console
WScript.Echo xml.transformNode(xsl)
end if
'etc etc
[/tt]
How much do you believe in statistic in this sort of thing?

 

[chigley]

For the record > XSL file is as follows

<xsl:stylesheet version="1.0" xmlns:xsl="

http://www.w3.org/1999/XSL/Transform"

xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema"
exclude-result-prefixes="rs">

<xslutput method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">

<xsl:for-each select="xml/rs:data/z:row">

<Company>

<xsl:attribute name="companyname">
<xsl:value-of select="@CustomerName"/>
</xsl:attribute>

</Company>

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>

 

[chigley]

Also, forgot to mention I have put in a msgbox

msgbox xml.transformNode(xsl)

msgbox err.description

the second msbox is blank so there is no discernable error. The first messagebox reads

'?xml version="1.0"?'

If i used MSXML2.DOMDocument.3.0 I get

'?xml version="1.0" encoding="UTF-16"?'

I should be getting

'?xml version="1.0"?'
<Company companyname="Some random company"></Company>
..
<Company companyname="Some random company"></Company>

 

[tsuji]

Your use of err object is wrong. Either you use "on error resume next" and "on error goto 0" in conjunction with capturing err object, or it would mean anything.

Besides, parseerror would _not_ be captured by err object. It is captured by the microsoft.xmldom or its sibling object.

 

[chigley]

do you have some code for me?

msgbox xml.transformNode(xsl)

*****

msgbox xml.SomeErrorFunctionYouNeedToTellMeAbout

******

 

[tsuji]

I have shown you, mind you?...

 

[chigley]

Yes you have, sorry. Am working on this now.

 

[chigley]

Bingo.

File not found error on loading the xml file. Failed silently with err.description not picking it up at all. Parseerror code you gave was excellent. Thanks for your help tsuji.