SQL and XML
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: SQL and XML

  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Question SQL and XML

    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

  2. #2
    Join Date
    May 2011
    Location
    Washington State
    Posts
    220

    Re: SQL and XML

    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.

Tags for this Thread

Posting Permissions

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


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center