ms access- convert record to xml string

[Guest]

Hi everybody,
I have need to take an entire record of a MS Access table/query and convert
it programmaticaly to XML. Now, I don't want to *export* the XML, what I want
is, to return a *string* object containing the unparsed XML as a string of
text to eventually be used in the same procedure. I might need to modify it a
bit and then dump it elswhere in the database.
If anybody has a suggestion how to go about this, I'd appreciate it.
I know I can use Acess' ExportXML method to export it to a file then read it
as plain text and then kill the file. I am hoping that there is a more
elegant solution, though.

 

[Tim Ferguson]

Now, I don't want to *export* the XML, what I want
is, to return a *string* object containing the unparsed XML as a
string of text to eventually be used in the same procedure. I might
need to modify it a bit and then dump it elswhere in the database.

select
"<record>" &
"<field1>" & field1 & </field1> &
"<field2>" & format(field2, someAppropriateFormat) & "</field2>" &
"</record>" as XmlString
from MyTable
where Something = true


dim rs as dao.recordset
set rs = db.Openrecordset("myXmlQuery")
do while not rs.Eof
debug.print rs("XmlString")
rs.movenext
loop

rs.close


You will probably need some kind of filter to prevent illegal characters
like & and < showing up in the data from the fields. Probably some kind
of Replace() function.

Hope that helps.

Tim F