Exporting to XML using a predefined XSLT file

[LittleSmudge]

I'm using A2k2 on Windoz XP

I need to Export some data to an XML file.

I have the .XSLT file which specifies the formatting / structure that the XML file must comply with.

I though I needed to use the Accesss ExportXML Method in VBA.

The help file seems to suggest that I need to refer to the XSLT file in the "DataTransform" parameter.

However my version of Access (2002) does not provide the option for specifying this parameter and it doesn't recognise it if I define it explicitly.


How do I tell Access the format I need it to put the XML file into ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[SiJP]

Output your XML to a file first.. then:

    Dim myXML as MSXML2.DOMDocument
    Dim myXSLT as MSXML2.DOMDocument

    Set myXML = New MSXML2.DOMDocument
    myXML.async = False
    myXML.preserveWhiteSpace = False
    
    Set myXSLT = New MSXML2.DOMDocument
    myXSLT.async = False
    myXSLT.preserveWhiteSpace = False

    myXSLT.Load "C:\my.xslt" 'Load the XSLT
    myXML.Load "C:\old.xml" 'Load the XML to be converted
    myXML.transformNodeToObject(myXSLT.documentElement, myXML) 'transform the XML within the DOM obj
    myXML.Save "C:\new.xml" 'Save the transformed XML

HTH's

------------------------
Hit any User to continue

 

[SiJP]

Alternatively, if you dont need to export your XML first, you can put the data into an ado recordset, and then call ado's .save method to load the data directly into the myXML dom obj (e.g. rs.Save myXML, adPersistXML)

This will negate the need to call the myXML.Load method.



------------------------
Hit any User to continue

 

[LittleSmudge]

SiJP

Your advice is not forgotten - I've finally got back down the stack of interuptions to attend to this issue.

However, I can't seem to work out which library I need to link to in order to get the MSXML recognised.

What do I need ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[SiJP]

Hey LittleSmudge,

I use the Microsoft XML, v4.0 library (msxml4.dll)

------------------------
Hit any User to continue

 

[LittleSmudge]

Thanks SiJP

I'm new getting other problems

rst.Save myXML, adPersistXML ' Loads data to DOM Doc from ado recordset
gives me a 438 Object doesn't support this property or Method error.


So I've gone for the

rst.Save "H:\Cship\ExportXML\TempNew.xml", adPersistXML
myXML.Load "H:\Cship\ExportXML\TempNew.xml"

myXML.transformNodeToObject myXSLT.documentElement, myXML

myXML.Save "H:\Cship\ExportXML\new.xml"

The "myXML.transformNodeToObject" line then gives me an Error 5 Invalid procedure call or argument.
( I had to take the brackets out to get it to compile )


Clue.
When the code breaks and I go into debug mode and hover over the "myXSLT.documentElement" part of the line the popup box shows
"myXSLT.documentElement = Nothing"

Is this telling me I have a problem in the XSLT file I've been given ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[SiJP]

Hia,

The 438 error is a new one on me. By the looks, it's not your ADO recordset giving you problems as you're able to save using a direct file reference, so I would make a starting point of an assumption the 'myXML' object is an issue.

Things to check:
a) Is is declared correctly?
b) Has it been set?

Really don't mean to insult your intelligence with any of these, but I'm forever missing the obvious myself!!

With the transform function, it looks like the xslt file hasn't loaded into your myXSLT doc obj.

Step through the code, and when you get to the myXSLT.Load "C:\my.xslt" line, check that your file does actually load ok (Check afterwards by seeing if myXSLT is nothing, and if not, whether ?myXSLT.XML in the immediate window gives you any output).




------------------------
Hit any User to continue

 

[LittleSmudge]

When I'm wantering down roads that I've never stepped on before - its statements of the "blindingly b.. obvious" that I need


The whole code is as follows

Private Sub Form_Load()
Dim myXML As MSXML2.DOMDocument
Dim myXSLT As MSXML2.DOMDocument

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT * FROM qryTestExport "
Debug.Print rst.RecordCount

Set myXML = New MSXML2.DOMDocument
myXML.async = False
myXML.preserveWhiteSpace = False

Set myXSLT = New MSXML2.DOMDocument
myXSLT.async = False
myXSLT.preserveWhiteSpace = False

myXSLT.Load "H:\Cship\Resultsdata.xslt"

' rst.Save myXML, adPersistXML   This line does not work as noted above
rst.Save "H:\Cship\ExportXML\TempNew.xml", adPersistXML
myXML.Load "H:\Cship\ExportXML\TempNew.xml"

myXML.transformNodeToObject myXSLT.documentElement, myXML

myXML.Save "H:\Cship\ExportXML\new.xml"

End Sub

I've created a test form for test working through this. The code gets launched as soon as the form loads.


?myXSLT.XML in the immediate window give myXSLT = ""

So it seems like there is a problem in the .XSLT file.
Interestingly it doesn't give any kind of error message even if I deliberately missname to file it should look for.
However, even having treble checked the folder path and file name - it doesn't load.

( ?myXML.XML in the immediate window gives the data I'd expect so that bit is working fine. )



On the rst.Save line issue
When I type in the rst.Save the auto complete comes up looking for [FileName As String]
So sticking myXML in there is of the wrong datatype for a start.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

Update on the rst.Save line issue
When I type in the rst.Save the auto complete comes up looking for [FileName As String]
So sticking myXML in there is of the wrong datatype for a start.

I've updated my Library reference of a later version ActiveX Data Objects 2.7 instead of 2.1
and now it asks for a [Destination] which accepts the myXML object - so that's working fine.

Still have problems getting .XSLT to load though.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[SiJP]

Heh, I was about to cross check your references with mine for the ADO stuff

Re: xslt.. have you got somthing like XML Spy? A commercial XML reader is always an advantage when working with XML!

If you haven't you can download it (google for XML spy)

If you have.. try opening the xslt up, and check that it is well formed. I've come across the com object not wanting to load xml that's not well formed before..

Worth a shot!

------------------------
Hit any User to continue

 

[LittleSmudge]

XML Spy downloaded - and it confirms that there is something iffy about the .XSLT file.

I Also have a .XLD file which contains all of the same lines as the .XSLT except that in the .XSLT the line start with a hyphen

- <xs:simpleType name="stringtype">
<xs:restriction base="xs:string" />
</xs:simpleType>

whereas the .XLD has tab indentations

<xs:simpleType name="stringtype">
<xs:restriction base="xs:string"/>
</xs:simpleType>


What is supposed to be the difference between them ?




If I use the .XLD file in the code we've been discussion it does go in but the output into new.XML looks just like another .XLD file rather than including the DATA in it.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[SiJP]

Try removing the hyphen from the xslt, then add this line to the top of the file: <?xml version="1.0"?>

The xld file I've no idea about I'm afraid!!


------------------------
Hit any User to continue

 

[LittleSmudge]

The first couple of lines already are

<?xml version="1.0" encoding="UTF-8" ?>
- <xs:schema xmlns:xs="

http://www.w3.org/2001/XMLSchema">

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[SiJP]

Cool bananas..

When you 'check wellformed' in XMLSpy, it should give you the line that is causing the issue, or at least an error message.

I can't say I'm brilliant with XML, but I can ive it a shot if you still have a problem with it!

Si

------------------------
Hit any User to continue

 

[LittleSmudge]

I've found out the the XML file I have got - even incorrect as it is - is for converting the XML into a CSV. So it won't help for making the XML in the first place.


I guess what I could do with is finding something that will take an XML file and back engineering a XSLT that will create it !



I think I'm going to have to live with the hard coded line by line file writes that I'm doing at the moment.

At least with that, the XML file is created, looks correct and agrees with the XSD
( the XSD is the schema file that contains all the type definitions and validation rules etc )




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[SiJP]

Here's an example XSLT that I have, that might suit your needs... just change the recordset field names (e.g. @FirstName), and the elements and you are away.

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

http://www.w3.org/TR/WD-xsl">

<xsl:template match="/">
<xsl:for-each select="//rs:data">
<xsl:apply-templates/>
</xsl:for-each>
</xsl:template>
<xsl:template match="//z:row">
<FirstName>
<xsl:value-of select="@FirstName"/>
</FirstName>
<Surname>
<xsl:value-of select="@Surname"/>
</Surname>
<Gender>
<xsl:value-of select="@MaleorFemale"/>
</Gender>
<Address>
<PostCode>
<xsl:value-of select="@PostCode"/>
</PostCode>
<HouseNumber>
<xsl:value-of select="@HouseNumber"/>
</HouseNumber>
<Street>
<xsl:value-of select="@Street"/>
</Street>
<TownCity>
<xsl:value-of select="@TownSity"/>
</TownCity>
</Address>
</xsl:template>
</xsl:stylesheet>




------------------------
Hit any User to continue

 

[LittleSmudge]

Do I have to replace the //rs:data with something relevant to my app ?

And the z:row part too ?


I've done the edits and seem to have something that Altova XMLSpy things is a goos XSLD file but it still outputs nothing after the transform.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[SiJP]

Hrmmm... Here's a little sample app to work with.. (rs:data refers to the recordset variable name...)

Open notepad, copy the following into it, and save as C:\test.xslt

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

http://www.w3.org/TR/WD-xsl">

<xsl:template match="/">
<Names xmlns="">
<xsl:for-each select="//rs:data">
<xsl:apply-templates/>
</xsl:for-each>
</Names>
</xsl:template>
<xsl:template match="//z:row">
<FirstName>
<xsl:value-of select="@FirstName"/>
</FirstName>
<Surname>
<xsl:value-of select="@Surname"/>
</Surname>
</xsl:template>
</xsl:stylesheet>


In a blank access db, create a table called tblTest, with two fields (both text fields, with a 50 char limit.. basically default). Call one field FirstName and the second field Surname.

Add one name into this table (e.g. "Jack" and "TheLad")

Create a new module, and copy the following code in:

Public Function xmlTest()

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim myXML As MSXML2.DOMDocument
Dim myXSLT As MSXML2.DOMDocument
Dim sSQL As String


Set myXML = New MSXML2.DOMDocument
myXML.async = False
myXML.preserveWhiteSpace = False

Set myXSLT = New MSXML2.DOMDocument
myXSLT.async = False
myXSLT.preserveWhiteSpace = False

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

sSQL = "SELECT * FROM tblTest"

rs.Open sSQL, cn
myXSLT.Load "C:\test.xslt"
rs.Save myXML, adPersistXML
Call myXML.transformNodeToObject(myXSLT.documentElement, myXML)
myXML.Save "C:\output.xml"
rs.Close
cn.Close

End Function

Run the function then check the output.xml file...
(Ensure that the msxml and ado references are set.)

------------------------
Hit any User to continue

 

[jdraw]

I have set up the example as described. tblTest, xslt file etc but
Get error 438 on line rs.Save myXML, adPersistXML
I'm trying to create a proc in A2002 to read a number of tables and export data to xml.

Can find little to no info on exporting table to xml in a vba procedure.

Any help and comments would be appreciated.

jdraw

 

[SiJP]

jdraw - I appreciate the thread is rather long, but LittleSmudge had this issue and resolved by updating his Library reference of a later version ActiveX Data Objects 2.7 instead of 2.1.

Good luck!

------------------------
Hit any User to continue