Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    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

    <XML>
    <manager>\\insertManagerfieldhere</manager>
    <customers>\\foreachmanagerinsertcustomershere</customers>
    </XML>

    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,

    Trin
    Attached Files Attached Files
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    This example should work

    'load the Microsoft Scripting Runtime library found in VBA module
    'Tools
    'References
    '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

    fnMakeXMLFile()
    Dim fso As New FileSystemObject
    Dim txtstr As TextStream

    dim mydb
    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

    Myrecordset.movefirst
    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)
    With txtstr
    .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>"
    .WriteLine "</XML_FILE>"
    End With
    Set txtstr = Nothing
    exit do
    end if

    myrecordset.movenext
    loop

    myrecordset.close

    End Function
    Last edited by axsprog; 09-21-07 at 09:02.
    Dale Houston, TX

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •