converting MS Access table to XML

[plaiditude]

I need to create/modify an XML export from an MS Access 2007 table. I'm hoping someone can point me in the right direction.

We have done this before with other tables where there is only one transaction allowed per account. Very basiclly it looks something like: (very simplified, there are many sub elements)

<TRANS_SLP>
<TRANSACTION_DATA>
</TRANSACTION_DATA>
<ACCOUNT_INFO>
</ACCOUNT_INFO>
</TRANS_SLP>

This new table that I have to report on has multiple rows/transactions per account. So when I export to XML it looks something like this for one account:

<TRANS_SLP>
<TRANSACTION_DATA>
</TRANSACTION_DATA>
<ACCOUNT_INFO>
</ACCOUNT_INFO>
</TRANS_SLP>
<TRANS_SLP>
<TRANSACTION_DATA>
</TRANSACTION_DATA>
<ACCOUNT_INFO>
</ACCOUNT_INFO>
</TRANS_SLP>

So it completely repeats the whole thing. What I want to end up with is just the transaction data repeating for each transaction like this:

<TRANS_SLP>
<TRANSACTION_DATA>
</TRANSACTION_DATA>
<TRANSACTION_DATA>
</TRANSACTION_DATA>
<TRANSACTION_DATA>
</TRANSACTION_DATA>
<ACCOUNT_INFO>
</ACCOUNT_INFO>
</TRANS_SLP>

What is the best method to do something like this? Is this something I can do with XSLT, do I need to add some code to MS Access and programatically export to XML? I would classify myself as a beginner at this (I did COBOL programming 8 years ago - not much help with this I've done some coding in MS Access before but have never done anything in XML. I'm reading the

http://www.w3schools.com

tutorials to understand some basics but I'm not sure where to begin with this) Hopefully my question makes some sense, THANKS for any help!

 

[Diancecht]

I cannot offer you a concrete answer, but looks like you should be able to handle this with MS Access standard export functionality.

Cheers,
Dian

 

[plaiditude]

The standard MS Access export only gives me the result of the second example in my first email. It exports each row as a separate element. What I need is for it to group all of the transactions together for each account.

 

[tsuji]

Support of xslt integrated with access:

http://office.microsoft.com/en-gb/access-help/using-xml-and-access-HA001034560.aspx

Technique for grouping with xsl element key for efficiency:

http://www.jenitennison.com/xslt/grouping/muenchian.html