(Note: This tip assumes that you have an ActiveConnectionsetup to your DSN.)
This function creates an XML document from an ADO Recordset, programmatically.
Public Function CreateXML(strRoot As String,strSQLStatement As String, OptionalstrConnectionString As String) As String Dim rstADO As ADOR.Recordset Dim fld As ADOR.Field Dim strXML As String If IsMissing(strConnectionString) Then strConnectionString = "ActiveConnectionString" End If Set rstADO = New ADOR.Recordset rstADO.Open strSQLStatement, strConnectionString,adOpenStatic, adLockReadOnly, adcmdText
Begin the XML declaration. This is required and indicates an XML document.
strXML = ""
Check for available records.
If rstADO.EOF And rstADO.BOF Then
Produce an empty element if there’s no data.
strXML = strXML & vbNewLine & "<" & strRoot &"s>" strXML = strXML & vbNewLine & "" & strRoot &"s>" Else
Open the first entity tag.
strXML = strXML & vbNewLine & "<" & strRoot &"s>"
Loop through the recordset and output them to XML with rstADO until .EOF. Begin the element.
strXML = strXML & vbNewLine & "<" &strRoot & ">"
Dump each field of the recordset for each fld in .Fields.
strXML = strXML & vbNewLine & "<"& fld.Name & ">" strXML = strXML & fld.Value strXML = strXML & "" & fld.Name& ">" Next fld
Close the element.
strXML = strXML & vbNewLine & "" &strRoot & ">"
Move the next Record.
.MoveNext Loop
Close the entity.
strXML = strXML & vbNewLine & "" &strRoot & "s>" .Close End With Set fld = Nothing Set rstADO = Nothing End If CreateXML = strXML End Function