Thread: Formatting query to XML file
09-20-07, 13:12 #1Registered User
- Join Date
- Oct 2005
Unanswered: Formatting query to XML file
Here's the challenge (my word for "problem").
In the attached DB, I have a query, that I need to format to a specified standard XML, and I haven't got a clue on how to build an XSL file (or is it XST?) for the purpose.
Basically there are 12 different offices across the world, each with their own system. We've agreed on a standard exchange format in XML where:
There's a manager node, with X number of customer children. (not the right XML jargon, sorry, but I'm lost here).
Something in likes of
I click a form button in which the code uses docmd.transferspreadsheet export........ that uses the XSL file (or whatever) to export the data to an XML file...
In short, how do I create the "template" to build the XML from.
Thanks in advance,
TrinIT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....
09-21-07, 07:58 #2Grand Poobah
Provided Answers: 5
- Join Date
- May 2003
This example should work
'load the Microsoft Scripting Runtime library found in VBA module
'Keep in mind that I have not include things such as vbTab (tab or indent) or vbspace for spaces
'some xml translation utilities or readers may be sensitive to that
'I did not look at your file - and used an example of three fields - one for criteria match and the other two for data being written.
'Good Luck - I hate xml
Dim fso As New FileSystemObject
Dim txtstr As TextStream
set mydb = currentdb
Strs = "Select * from mytablename"
Set MyRecordset = MyDb.OpenRecordset(Strs)
dim myCrit1 , mydata1, mydata2
set mycrit1 = something maybe from a form or control
do until myrecordset.eof
if myrecordset!username = mycrit1 then
mydata1 = myrecordset!Field_FirstName 'example
mydata2 = myrecordset!Field_lastName 'Example
Set txtstr = fso.CreateTextFile("c:\" & "myxmlfile.xml", True)
.WriteLine "<XML_FILE>" ' 'different formats may have different tag here
.WriteLine "<USER_ID>" & mycrit1 & "</USER_ID>"
.WriteLine "<User_FName>" & mydata1 & "</UserFName>"
.WriteLine "<User_LName>" & mydata2 & "<User_LName>"
Set txtstr = Nothing
Last edited by axsprog; 09-21-07 at 08:02.Dale Houston, TX