asked on

Exporting Access query to XML

I'm working on some code to write the results of an Access query to an XML file. If this was going to be a flat file, then I'd be done, but I need to be able to provide detail info, something like:

<Claim_Input>
    <Case_Num>1</Case_Num>
    <Address>
        <Street>112 Test Road</Street>
        <City>New York</City>
        <State>NY</State>
    </Address>
    <SomethingElse>3</SomethingElse>
</Claim_Input>

Open in new window

I've configured my column names like:

Case_Num
Address|Street
Address|City
Address|State
SomethingElse

Open in new window

I'm trying to devise a function which will parse the address field names into a structure which looks like the above. I'm totally flexible and can rewrite the query to return the column names differently, but this seemed like it would work.

I'm just having a brain cramp and cannot get this figured out.

If it was just this one case (address), I'd do something like:

If fields(n).Name = "Address|Street" Then
    Print #1, "<Address>"
    Print #1, "    <Street>" & rs(fields(n).name) & "</Street>
elseif fields(n).name = "Address|City" Then
    Print #1, "    <City>" & rs(fields(n).name) & "</City>
Elseif fields(n).name = "Adderss|State" Then
    Print #1, "    <State>" & rs(fields(n).name) & "</State>
    Print #1, "<Address>"
end if

Open in new window

But I would like to be able to make this more generic so that if I pass a function the values:

Call WriteToXML("Case_Num", 1)
Call WriteToXML("Address|Street", "113 Test Road")
Call WriteToXML("Address|City", "New York")
Call WriteToXML("Address|State", "NY")
Call WriteToXML("SomethingElse", 3)

Open in new window

it would write to the XML file similar to my code segment. I don't know enough about XML to write my own style sheet. BTW, there will be cases where the data is nested 3 deep.

Thanks,
Dale

Ryan Chong

since your XML structure won't change frequently, I'm thinking if you should declare a class to handle it, and then just looping the class' properties to generate the XML. that could be the way to go?

Dale Fye

ASKER

Ryan,

I'm open to ideas,but have no idea where to start regarding your suggestion.

Dale

Ryan Chong

a quick example would be like this:

clsAddress

Option Explicit


Public Street As String
Public City As String
Public State As String

Open in new window

clsClaim

Option Explicit


Public Case_Num As Integer
Public Address As New clsAddress
Public SomethingElse As String


Public Function GenerateXML() As String
    Dim tmp As String
    tmp = "<Claim_Input>" & vbCrLf
    tmp = tmp & vbTab & "<Case_Num>" & Case_Num & "<Case_Num>" & vbCrLf
    tmp = tmp & vbTab & "<Address>" & vbCrLf
    tmp = tmp & vbTab & vbTab & "<Street>" & Address.Street & "</Street>" & vbCrLf
    tmp = tmp & vbTab & vbTab & "<City>" & Address.City & "</City>" & vbCrLf
    tmp = tmp & vbTab & vbTab & "<State>" & Address.State & "</State>" & vbCrLf
    tmp = tmp & vbTab & "</Address>" & vbCrLf
    tmp = tmp & vbTab & "<SomethingElse>" & SomethingElse & "<SomethingElse>" & vbCrLf
    tmp = tmp & "</Claim_Input>" & vbCrLf
    GenerateXML = tmp
End Function

Open in new window

then call it like:

Sub test()
    Dim addr As New clsAddress
    
    addr.Street = "112 Test Road"
    addr.City = "New York"
    addr.State = "NY"
    
    Dim myClaim As New clsClaim
    myClaim.Case_Num = 1
    Set myClaim.Address = addr
    myClaim.SomethingElse = "3"
    
    Debug.Print myClaim.GenerateXML
End Sub

Open in new window

John Tsioumpris

For a rather complex case i had made a table which represents fields and order ,grouped by levels.
So my code reads the table..and makes the necessary XML "positioning".
If something changes since is table oriented...just a simple "shift" in the table is sufficient ( :) almost as each change comes with extra requirements...like adding values per group,complex lookups.... )
Also something else because i haven't seen mentioned : use MSXML2.DOMDocument60 to construct the XML

Dale Fye

ASKER

Well, it turns out that although there were only about 20 fields of data to transmit, the desired XML file contains over 250 entries, 230 of which are null.

So I simply copied the XML from Notepad ++, pasted it into a code module and then pasted the following as a prefix to each line:

     Print #FF, "                   "

Open in new window

After that, I searched for the 20 fields that needed actual data and inserted a reference to the recordset in the string:

     Print #FF, "                   <SomeField>" & rs!someField & "</SomeField>"

Open in new window

It is working great.
Thanks for your assistance

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

ASKER

I'm surprised that I didn't get an option to award points to you guys after accepting my own solution. I'll have check with the admins.