Export Oracle database table as XML file using Python?

Question

I'm trying to export a table held within an Oracle 12c database into constituent XML files, such that each row in the Oracle table produces 1 XML file. To do this, I am using the Python 2.7 library xml.etree.ElementTree, but I can't see anything in the documentation which would allow me to do this. Essentially all I require at this point is:

import cx_Oracle
from xml.etree import ElementTree as ET

SQL = ''.join([ 'SELECT * FROM ', table_name ])
connection = cx_Oracle.connect('username/password@database')
cursor = connection.cursor()

for i in range(num_rows):

    ... #code works fine up to here

    file_name = i
    file_path = ''.join([ 'C:\..., file_name, '.xml ])
    file = open(file_path, 'w')
    cursor.execute(SQL)
    ET.ElementTree.write(file) #This line won't work
    cursor.close()
    file.close()

connection.close()

I know it will only be 1 line of code - I'm just really not sure what to do.

As an added complication, I can unfortunately only use libraries native to Python 2.7 such as etree - I'm not able to download 3rd party Python libraries at work. Thanks in advance for any help or advice.

write() is a method on ElementTree instances, but you call it on the class. And the root element of the XML structure that represents your table row should be an argument. Does ET.ElementTree(root).write(file) work (assuming that root is that root element)? — mzjn
– mzjn, Commented Jul 18, 2017 at 19:06

Mick Greener · Accepted Answer · 2017-07-20 11:18:50Z

[SOLVED] For future reference, there were two separate steps required to export Oracle data into xml format using Python and cx_Oracle.

1) First, for some reason, in the initial SQL statement within Python, we must use an alias with the XMLtype table we are trying to manipulate, as well as adding .getClobVal() into the SQL statement (line 3), as outlined here in Kishor Pawar's answer. Hence the code above becomes:

1  import cx_Oracle
2 
3  SQL = ''.join([ 'SELECT alias.COLUMN_NAME.getClobVal() FROM XML_TABLE ])
4  connection = cx_Oracle.connect('username/password@database')
5  cursor = connection.cursor()

2) In my question I was using cursors wrong - hence the additional code at line 12 is required: cx_Oracle.Cursor.fetchone(). This actually returns a tuple, hence we require the [0] at the end to slice out the single piece of information contained within the tuple.

Furthermore, this needs to be converted to a string using str() (line 13).

After this is done, no other imports such as ElementTree are required to generate the xml files; this is done in lines 15-16.

6  for i in range(num_rows):
7 
8      file_name = i
9      file_path = ''.join([ 'C:\..., file_name, '.xml ])
10     file = open(file_path, 'w')
11     cursor.execute(SQL)
12     oracle_data = cx_Oracle.Cursor.fetchone(cursor_oracle_data)[0]
13     xml_data = str(oracle_data)
14
15     with open(file_path, 'w') as file:
16         file.write(xml_data)
17
18     file.close()
19
20 cursor.close()
21 connection.close()

Christopher Jones · Accepted Answer · 2017-07-19 03:19:41Z

0

Have you considered returning the XML from the database? Oracle DB has a bunch of XML support.

These two queries show different features; check the Oracle Manuals for others:

select xmlelement("Employees",
 xmlelement("Name", employees.last_name),
 xmlelement("Id", employees.employee_id)) as result
 from employees
 where employee_id > 200

and

 select dbms_xmlgen.getxml('
 select first_name
 from employees
 where department_id = 30') xml
 from dual

answered Jul 19, 2017 at 3:19

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Collectives™ on Stack Overflow

Export Oracle database table as XML file using Python?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related