Add support to XML type

[rafaelreuber]

Hi,

The XML type is not supported on cx_Oracle. When I run a code like below, I get the error: AttributeError: 'cx_Oracle.OBJECT' object has no attribute 'read'

result = cursor.execute(
  """SELECT XMLElement("Date", hire_date)
  FROM hr.employees
  WHERE employee_id = 203"""
 )

for res in result:
	# The exception occours right here
	raw_xml = res[0].read()

But there are simple workaround. Just use the function GETCLOBVAL:

result = cursor.execute(
  """SELECT XMLElement("Date", hire_date).GETCLOBVAL()
  FROM hr.employees
  WHERE employee_id = 203"""
 )

for res in result:
	import xml.etree.ElementTree as ET
	xml_string = res[0].read()
	tree = ET.fromstring(xml_string)

The workaround works. But I think that would more pythonic if the cx_Oracle do this automatically:

result = cursor.execute(
  """SELECT XMLElement("Date", hire_date)
  FROM hr.employees
  WHERE employee_id = 203"""
 )

for res in result:
	xml = res[0].read()
	type(xml) # <class 'xml.etree.ElementTree.Element'>

[anthony-tuininga]

XMLType is returned as an Oracle object with no attributes. It has functions that can only be called from SQL or PL/SQL -- which defeats the purpose! The workaround you have noted already works for you. Another one is to use an output type handler, as in this code:

import cx_Oracle

def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
    if defaultType == cx_Oracle.OBJECT:
        return cursor.var(cx_Oracle.LONG_STRING, arraysize = cursor.arraysize)

conn = cx_Oracle.Connection("cx_Oracle/dev@localhost/orcl")
conn.outputtypehandler = OutputTypeHandler
cursor = conn.cursor()
cursor.execute("select XMLCol from testxml where rownum <= 1")
row = cursor.fetchone()
print(row)    # will be returned as a string

I will look into making this happen automatically since the XMLType objects returned aren't helpful by themselves. In the meantime, hopefully this is a helpful addition.

[ghost]

+1

[zhaowei198311]

yes

[gitrevisor]

Any update on this? Would be really nice to have....

[anthony-tuininga]

I did some initial research last year and discussed with the XMLType group internally. A few enhancements to the OCI library are planned which will make working with this type simpler. In the meantime, however, I can look at better workarounds. Thanks for bringing it back to my attention. I'll take another look at it. :-)

[anthony-tuininga]

I just created a patch to ODPI-C (which cx_Oracle depends on) which automatically intercepts SYS.XMLTYPE and returns a (LONG) string instead. It is awaiting internal review but I hope to have it available publicly in a few days. Thanks for your patience.

[gitrevisor]

Excellent news! Thanks for your hard work!

[rafaelreuber]

Awesome!

[anthony-tuininga]

The patch has landed. Let me know if you have any difficulties with it!

[gitrevisor]

The patch is brilliant except when trying to load xml files larger than 4000 bytes from an Oracle 12c database I get ORA-19011: Character string buffer too small. Any way around this? Thanks

[anthony-tuininga]

Yes, I noticed that myself in my own testing. If you enable extended string functionality you can get up to 32 KB, but I'm not aware of any way to go beyond that currently. I've asked internally if/when this restriction is going to be lifted.

[gitrevisor]

Thanks for your work on this.

[anthony-tuininga]

Implemented in cx_Oracle 7.1 which was released yesterday.

[batatan]

The patch is brilliant except when trying to load xml files larger than 4000 bytes from an Oracle 12c database I get ORA-19011: Character string buffer too small. Any way around this? Thanks

I have this issue in a django app when I map a TextField or a CharField to a sys.xmltype field

[anthony-tuininga]

You can increase the size to 32,767 bytes if you set max_string_size to extended. This is available in Oracle Database 12.1 and higher. Unfortunately, there is no other way to increase the size available at the moment. You can, of course, use getClobVal() to get the data as a CLOB which is not limited to size -- but isn't seamless either.