Export table to XML programmatically

[gan]

Hi, I'd like to know how to write a program which similar to the new feature
in Access XP, where user is allowed to export table (or query) to XML file.

Thanks in advance!!

 

[Juan Paulo]

See this example taken from one Book of Rick Dobson
(Advanced Programming in Access - Microsoft (c))
The most important is the "adPersistXML"

Sub ForShippersSave()
Dim rst1 As ADODB.Recordset
Dim varLocalAddress As Variant
Dim int1 As Integer

'Open a recordset on the Shippers table in
'the current project
Set rst1 = New ADODB.Recordset
rst1.Open "Shippers", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable

'Make a new .xml file based on the table
varLocalAddress = "c:\Inetpub\wwwroot\pma10\" & _
"ShippersSave.xml"
Kill varLocalAddress
rst1.Save varLocalAddress, adPersistXML

'Clean up objects
rst1.Close
Set rst1 = Nothing

End Sub

 

[Rick Brandt]

Hi, I'd like to know how to write a program which similar to the new feature
in Access XP, where user is allowed to export table (or query) to XML file.

Here is some code I started on about a year ago. I don't have access to my
finished module at home so I want to emphasize that this is _very_ basic and
would need a lot of fleshing out. It is a starting point though. It uses DAO.

The biggest thing it would need added is a separate function I called
ProcessDataForXML(). You basically need to pass all values being output to the
file through that function to properly escape all illegal characters in XML.
There are quire a few characters that are not allowed in XML and they either
need to be replaced with an escape sequence or stripped out of the data
altogether. You can do a web search and find the list of characters pretty
easily.

This produces what I call "tabular XML" where each row in the table is a node
and each value is a parameter in that node.


***CODE STARTS HERE***
Function TableToXML(TableName As String, Optional Filter As String)

On Error GoTo ErrHandler

Dim db As Database
Dim MySQL As String
Dim rs As Recordset
Dim fld As Field

MySQL = "SELECT * FROM " & TableName
If Len(Filter) > 0 Then
MySQL = MySQL & " WHERE " & Filter
End If

Set db = CurrentDb
Set rs = db.OpenRecordset(MySQL, dbOpenSnapshot)

If rs.EOF = False Then
Open "C:\" & TableName & ".xml" For Output As FreeFile
Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & ">"
Print #1, " <" & TableName & ">"
Do Until rs.EOF = True
Print #1, " <ROW>";
For Each fld In rs.Fields
Print #1, fld.Name & "=" & Chr(34) & fld & Chr(34);
Next fld
Print #1, "</ROW>"
rs.MoveNext
Loop
Print #1, " </" & TableName & ">"
End If

Egress:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Close #1
Exit Function

ErrHandler:
MsgBox Err.Description
Resume Egress
End Function

 

[Gan]

Hi, but I saw the content (or XML format) is different than the output been
exported using access XP. And this will cause me a problem if I try to
import back to another database. Any idea??
Thanks.

 

[Gan]

Hi, originally I'm using this method but I found that it takes too long time
to process (ps: I've up to few hundreds records in the table). That's why
I'm looking for alternative. Any other idea? Thanks

 

[Rick Brandt]

Hi, originally I'm using this method but I found that it takes too long time
to process (ps: I've up to few hundreds records in the table). That's why
I'm looking for alternative. Any other idea? Thanks

I don't understand that. I've used it against tables with thousands of
records and it has always been almost instantaneous for me.

 

[Kyle Burns]

XSL

Hi, but I saw the content (or XML format) is different than the output been
exported using access XP. And this will cause me a problem if I try to
import back to another database. Any idea??
Thanks.