Export to XML

[Guest]

Good morning,

Is there a way or does someone have some sample code to export a table or
qry results into an xml file.

I tried the built in command (htm, xml, xsl). However, it creates a file
that only works on IE and I need to create it to work on IE, Mozilla,
Opera.....

Thank you,

Daniel

 

[Rick Brandt]

Good morning,

Is there a way or does someone have some sample code to export a
table or qry results into an xml file.

I tried the built in command (htm, xml, xsl). However, it creates a
file that only works on IE and I need to create it to work on IE,
Mozilla, Opera.....

It is fairly easy to open a Recordset in code and loop through it while
writing the data out to a file along with the XML element tags. Here's a
sample routine from one of my apps that should get you started...

Function XMLOutput(QryOrTblDef As String, Optional TableName As Variant) As
String

Dim MyDB As Database
Dim rst As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(QryOrTblDef, dbOpenSnapshot)

strText = "<?xml version=""1.0""?>" & vbCrLf

If IsMissing(TableName) = True Then
MyTableName = QryOrTblDef
Else
MyTableName = TableName
End If

strText = strText & "<" & MyTableName & ">" & vbCrLf
With rst
Do Until .EOF
For Each fld In rst.Fields
If Left(fld.Name, 2) <> "X_" Then
strText = strText & " <" & fld.Name & ">" & _
XMLEscape(Nz(rst(fld.Name), "NULL_VALUE"), True) & "</"
& fld.Name & ">" & vbCrLf
End If
Next
.MoveNext
Loop
End With

strText = strText & "</" & MyTableName & ">" & vbCrLf

Set rst = Nothing
Set MyDB = Nothing

XMLOutput = strText

End Function

 

[Guest]

Rick,

I keep getting an error "Sub or Fundtion Not defined" about the 'XMLEscape'

Also, what method would you use to write the text file fso. or other (I've
never done this using vba before).

Thank you,

Daniel

 

[Guest]

Rick,

About the reference libraries. I added DAO, do I need any others?

Daniel

 

[Rick Brandt]

Rick,

I keep getting an error "Sub or Fundtion Not defined" about the
'XMLEscape'

Sorry I forgot that I was using another custom function in there. In this
case it is a custom function to escape out special characters not allowed in
XML. Here is an example routine that writes the result to a file on the
root of your C drive. I have removed the custom function for the sake of
the example. This one uses attributes for fields instead of elements. I
have another that uses elements if you want that.

(with untested changes but pulled from a working app)

Function XMLAttributeOutputToFile(QryOrTblDef As String, Optional TableName
As Variant) As String

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(QryOrTblDef, dbOpenSnapshot)

Open "C:\" & TableName & ".xml" For Output Shared As #1

Print #1, "<?xml version=""1.0""?>"

If IsMissing(TableName) = True Then
MyTableName = QryOrTblDef
Else
MyTableName = TableName
End If

Print #1, "<" & MyTableName & ">"

strText = strText & "<" & MyTableName & ">" & vbCrLf
With MyRS
Do Until .EOF
strText = " <Row"
For Each fld In MyRS.Fields
strText = strText & " " & fld.Name & "=" & Chr(34) &
MyRS(fld.Name) & Chr(34)
Next
Print #1, strText & "></Row>"
.MoveNext
Loop
End With

Print #1, "</" & MyTableName & ">"

Egress:
On Error Resume Next
Close #1
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
Exit Function

ErrHandler:
MsgBox Err.Description
Resume Egress
End Function