SQL and XML

[dmonder]

I would like to convert a SQL query (which could come from Oracle, SQL Server, Access, etc.) and save it as an XML. I have figured the basics out but I am having a problem with a certain situation.

My SQL query returns data like the following:
username,AC_YEAR,RANK,ADMINDEP_COLLEGE,ADMINDEP_DEP
johndoe,2011-2012,Instructor,Arts,Philosophy
johndoe,2010-2011,Instructor,Arts,Philosophy
janedoe,2011-2012,Professor,Arts,English

The XML that would be generated:
<Data>
<Record username="johndoe">
<ADMIN>
<AC_YEAR>2011-2012</AC_YEAR>
<RANK>Instructor</RANK>
<ADMINDEP>
<COLLEGE>Arts</COLLEGE>
<DEP>Philosophy</DEP>
</ADMINDEP>
</ADMIN>
<ADMIN>
<AC_YEAR>2010-2011</AC_YEAR>
<RANK>Instructor</RANK>
<ADMINDEP>
<COLLEGE>Arts</COLLEGE>
<DEP>Philosophy</DEP>
</ADMINDEP>
</ADMIN>
</Record>
<Record username="janedoe">
<ADMIN>
<AC_YEAR>2011-2012</AC_YEAR>
<RANK>Professor</RANK>
<ADMINDEP>
<COLLEGE>Arts</COLLEGE>
<DEP>English</DEP>
</ADMINDEP>
</ADMIN>
</Record>
</Data>

NOTE1: The record tag is not repeated for multiple rows for the same username.
NOTE2: The ADMINDEP fields in the SQL become embedded as subtags of <ADMINDEP>.

Right now, I have embedded some characters in the field names to trigger certain behaviors but was wondering if anyone had a better way to do this?

All help is appreciated!
David

[fcronin]

How are you currently creating the XML from your query results?

There would be many ways to approach this, without knowing what you are currently doing, I'm not sure if I have any new thoughts for you... I can say, however, that naming your fields to determine XML layout should not be needed in your scenario since you seem to want to key off of the user name, you simply need to add data to the user name node if it exists, or create a new node if it doesn't.