# -----------------------------------------------------------------------------
# Name         :  sql2xml.py
# Version      :  .0.1
# Description  :  returns an SQL query to xml
# Requirements :  Python 2.0, db.py
# -----------------------------------------------------------------------------
# Author       :  Scott Hathaway
# Contact      :  slhath@home.com
#                 http://members.home.net/slhath/
# -----------------------------------------------------------------------------
# Classes: n/a
# -----------------------------------------------------------------------------
# Functions:
#    sql2xml - takes an SQL query and converts it to xml.   
# -----------------------------------------------------------------------------
# Disclaimer:  The following code is provided into the public domain.  The
#              author shall not be responsible for any use or misuse of this
#              code.
# -----------------------------------------------------------------------------
# Note:  sql2dtd (which takes an SQL query and returns a valid XML DTD) is 
#        available from Dr. David Mertz at http://gnosis.cx/download/sql2dtd.py
# -----------------------------------------------------------------------------

from db import *

def sql2xml(dsn,un,pw,sql,fList,xmlFile,xmlFormat='element'):
    # dsn = the DSN
    # un = the username
    # pw = the password
    # sql = the SQL call
    # fList = the pipe separated field listing
    # xmlFile = the path and filename of the xml file to create
    # xmlFormat = can be 'element' or 'attribute' - this flag
    #             decides the format of the resulting xml
    data = dbCallAccess(sql,dsn,un,pw)
    fList = fList.replace(' ','_')
    fNames = fList.split("|")
    xml  = '<?xml version="1.0"?>\n'
    xml += '<sql2xml>\n'
    if(xmlFormat == 'element'):
        for j in range(0,len(data)):
            xml += '    <row number="' + str(j+1) + '">\n'
            for i in range(0,(len(fNames))):
                xml += '        <' + str(fNames[i]) + '>' + str(data[j][i]) + '</' + str(fNames[i]) + '>'
            xml += '    </row>\n'
    else:
        for j in range(0,len(data)):
            xml += '    <row number="' + str(j+1) + '" '
            for i in range(0,(len(fNames))):
                xml += str(fNames[i]) + '="' + str(data[j][i]) + '" '
            xml += '/>\n'
    xml += '</sql2xml>\n'
    try:
        result = open(xmlFile,'w').write(xml)
        return 1
    except:
        return 0