Export to XML file

[Ling]

Hi,

Any idea how to export data in MS Access to XML file using VB?

Thanks.

[Cakkie]

You can do this in a few ways.

1) You can use ADO, as of version 2.5, you can save an ADO recordset to XML. This however, gives you XML in the following way
...
<row name=Cakkie location=Belgium age=unknown />
<row name=Cimperiali location=Italy age=old />
...


this can be done with the save method, either directing it to a file, or directing it to a stream object.

2) you can use the XML DOM object. i'm not sure how this works, but you can get an XML structure like this
<PERSON>
<NAME>Cakkie</NAME>
<LOCATION>Belgium</LOCATION>
<AGE>unknown</AGE>
</PERSON>
<PERSON>
<NAME>Cimperiali</NAME>
<LOCATION>italy</LOCATION>
<AGE>old</AGE>
</PERSON>



3) You can get the same structure as above, doing it entirely in VB, that is, opening the recordset, loop through it, and write your data to a textfile in XML.
Your code should look something like this

private Sub WriteXML(rst as Recordset, Filename as string)
dim FFile as integer
FFile = freefile
open filename for output as #ffile

' xml header
print #ffile, "<xml>" & vbcrlf & " <records>" & vbcrlf

' loop through each record
do until rst.eof
print #ffile, " <record>"
dim fld as field
' loop through each field an output it to the file
for each fld in rst.fields
print #ffile, space(6) & "<" & fld.name & ">" & fld.value & "</" & fld.name & ">" & vbcrlf
next fld

' close the record
print #ffile, " </record>" & vbcrlf

rst.movenext

loop

' close the xml file
print #ffile, " </records>" & vbcrlf & "</xml>"
close #ffile

End Sub






Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Iouri]

You can create a recordset and save it in XML format


Rst1.Save "c:\Temp\Recordset.xml", adPersistXML

Iouri Boutchkine
iouri@hotsheet.com