return sql query in xml format in python

Question

When I first started working at the company that i work at now, I created a java application that would run batches of jasper-reports. In order to determine which parameters to use for each report in the set of reports, I run a sql query (on sqlserver). I wrote the application to take an xml file with a set of parameters for each report to be generated in the set. so, my process has become, effectively, three steps:

1. run the sql query and return the results in XML format (using 'for XML auto')
2. run the results of the sql query through an XSLT transformation so the xml is formatted in such a way that is friendly with the java application i wrote.
3. run the java application with that final xml file

As you can imagine, what I'd like to do is accomplish these steps in python, but i'm not quite sure how to get started. I know how to run an SQL query in Python. I see plenty of documentation about how to write your own xml document with Python. I even see documentation for xsl transformations in python.

the big question is how to get the results of the sql query in XML through python. Any and all pointers would be very valuable. Thanks, _Ramy

Answer 1

I would imagine that executing the sql query you have using 'FOR XML AUTO' will give you a recordset with one record in it (the xml). You would then retrieve the first record and continue with your application from there.

Example using pyodbc:

cursor.execute("select user_name from users where user_id=? for xml auto", userid)
xml = cursor.fetchone()
if xml:
    # do your xsl transformation and other processing here...

Edit

I selected a lot of fields in my test and (I think) managed to exceed some buffer size for the row objects, so it split the record.

Based on my test the code I would need would be:

cursor.execute("select user_name from users where user_id=? for xml auto", userid)
rows = cursor.fetchall()
xml = ''.join(row[0] for row in rows)